How to compare data for IDs across older period dates?

Question

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:

iddataperiod001Y20240122002Y20240122003Y20240122001Y20240123002N20240123003Y20240123001Y20240124002N20240124003N20240124001Y20240125002Y20240125003N20240125

toxicboy · Answer

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

ChrisTX · Answer

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

How to compare data for IDs across older period dates.yxmd

DataNath · Answer

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

Compare data changes from prev day.yxmd