topic Re: Comparison Metric Calculation in dashboard in QlikView

Comparison Metric Calculation in dashboard

polisetti — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Comparison Metric Calculation in dashboard

adamdavi3s — Wed, 15 Mar 2017 10:26:53 GMT

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

Re: Comparison Metric Calculation in dashboard

Gysbert_Wassenaar — Wed, 15 Mar 2017 10:35:47 GMT

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)

Re: Comparison Metric Calculation in dashboard

polisetti — Wed, 15 Mar 2017 10:47:40 GMT

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.

Re: Comparison Metric Calculation in dashboard

Gysbert_Wassenaar — Wed, 15 Mar 2017 12:40:45 GMT

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