How to compare data for IDs across older period dates?

[toxicboy]

Hello Everyone,

I have a database.table where data is getting appended every end of Business Days. I want to calculate in last 3 months what was the no of changes the column had every day, as compared to previous day?

As per the below sample data, 

1. while comparing 20240125 to its previous period 20240124, 1 value changed

2. while comparing 20240124 to its previous period 20240123, 1 value changed

3. while comparing 20240123 to its previous period 20240122, 1 value changed

Sample data be like:

id	data	period
001	Y	20240122
002	Y	20240122
003	Y	20240122
001	Y	20240123
002	N	20240123
003	Y	20240123
001	Y	20240124
002	N	20240124
003	N	20240124
001	Y	20240125
002	Y	20240125
003	N	20240125

[DataNath]

Hey @toxicboy - not exactly sure what format you're wanting for the output but here's something that'll allow you to do the analysis/checking you're after:

1) Summarize, grouping the data by period and data, getting counts of the data

2) Cross-Tab these so for each period we have a [N] and [Y] count column

3) Order the periods ascending

4) Use a Multi-Row Formula to take the difference between the current and previous period

[ChrisTX]

If you only need a Count of changes for each Id:

 

[toxicboy]

Hi @DataNath Thanks for your interest. I tried your approach, however in the multi-row stage, my data has changed and instead of Y/N. it has a changing string values. IN such case how too compare that data with its previous periods