Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Check out the P() and E() functions which can be used in set analysis
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)
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.
Maybe. It depends on your data model and your exact requirements.