Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
polisetti
Contributor

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
MVP & Luminary
MVP & Luminary

Re: Comparison Metric Calculation in dashboard

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
Honored Contributor

Re: Comparison Metric Calculation in dashboard

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

MVP & Luminary
MVP & Luminary

Re: Comparison Metric Calculation in dashboard

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

polisetti
Contributor

Re: Comparison Metric Calculation in dashboard

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.

MVP & Luminary
MVP & Luminary

Re: Comparison Metric Calculation in dashboard

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


talk is cheap, supply exceeds demand