Hi Qlik Experts,
I had a requirement to make a month-month comparison and generate reports on changes at entity level which caused variations in the KPI's shown on the Qlikview dashboard.
To simplify the ask with an example, below is the scenario.
MYSITETABLE_M1:
LOAD
*
INLINE
[
SITEID_M1,MONTH_M1,STATUS_M1
1,JAN 2018,Active
1,FEB 2018,Terminated
1,MAR 2018,Active
2,JAN 2018,Active
2,FEB 2018,Active
2,MAR 2018,Active
3,JAN 2018,Active
3,FEB 2018,Terminated
3,MAR 2018,Inactive
1,APR 2018,Active
2,APR 2018,Active
3,APR 2018,Inactive
4,APR 2018,Active
];
MYSITETABLE_M2:
LOAD
*
INLINE
[
SITEID_M2,MONTH_M2,STATUS_M2
1,JAN 2018,Active
1,FEB 2018,Terminated
1,MAR 2018,Active
2,JAN 2018,Active
2,FEB 2018,Active
2,MAR 2018,Active
3,JAN 2018,Active
3,FEB 2018,Terminated
3,MAR 2018,Inactive
1,APR 2018,Active
2,APR 2018,Active
3,APR 2018,Inactive
4,APR 2018,Active
];
Basically, MYSITETABLE_M1 and MYSITETABLE_M2 hold the same set of data where SITEID_M1 and SITEID_M2 are key columns though they are not linked in the model.
Now user is free to select any combination of MONTH_M1 and MONTH_M2 and would like to see below outcomes.
Report1 - Sites present in MONTH_M1 and absent in MONTH_M2 (E.g: MONTH_M1 is Apr 2018 & MONTH_M2 is JAN 2018).
Report2 - Sites present in both months but status got changed (E.g: MONTH_M1 is JAN 2018 & MONTH_M2 is FEB 2018).
I am able to produce both the reports by scripting i.e., by creating intermediate tables for each of the report which is consuming heavier processing time due to overall application volume and additional functional scripting.
Could someone advise a way to handle it over presentation layer. I am Ok to tweak the model to link these two tables (or) consider single table instead of two as both tables infact hold the same data sets. I am using personal edition, will be of help if you could provide your advise in expression/script/image.
Thanks in advance.
