Comparison Metric Calculation in dashboard

[polisetti]

Hi Everyone,

My requirement is to calculate comparison metrics(New, Deleted, Overlapping) in the dashboard by using Set Analysis.

I have 2 datasets Current and Previous. The key Column to calculate above metrics is Account_ID.The Structure of the tables is same. Now, I need write metrics like New(Account_ID which are present in Current file but not in Previous), Deleted Accounts (Accounts deleted in latest data month compared to previous ) and Overlapping Accounts(Accounts present in both data months)

Regards,

Polisetti

[adamdavi3s]

Check out the P() and E() functions which can be used in set analysis

[Gysbert_Wassenaar · Accepted solution]

You can probably calculate the status of an account in the script with something like this:

ExistingAccountIDs:

LOAD DISTINCT Account_ID as ExistingAccount FROM HistoricData;

AccountsTable:

LOAD

     Account_ID,

     Account_ID as NewOrExistingAccount,

     If(Exists(ExistingAccount, Account_ID), 'Existing', 'New') as AccountStatus,

     ...other fields...

FROM

     LatestData

     ;

CONCATENATE (AccountsTable)

LOAD

     Account_ID,

     'Deleted' as AccountStatus,

     ...other fields...

FROM

     HistoricData

WHERE

     Not Exists(NewOrExistingAccount, Account_ID)

     ;

DROP FIELD NewOrExistingAccount;

Then you can use the new field AccountStatus in set analysis expressions:

count({<AccountStatus={'New'}>}Account_ID)

[polisetti]

Thank you Gysbert,

I will try the above method and will share the outcome. Will p() and E() logic in set analysis work without doing data model changes?

Regards,

Polisetti.

[Gysbert_Wassenaar]

Maybe. It depends on your data model and your exact requirements.