Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator II
Creator II

Comparison Metric Calculation in dashboard

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
adamdavi3s
Master
Master

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

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
polisetti
Creator II
Creator II
Author

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.


talk is cheap, supply exceeds demand