Run time KPI Calculation is required

Anonymous — Thu, 15 Feb 2018 13:34:19 GMT

Hi Community,

I am new to QV however have good experience in VBA SQL automation.....

Looking help from you QV Expert people for one of my requirement which is easy for me to achieve in Excel but just look imposible for me in QV

So my requirement is :

I have Region and Country wise Monthly Data for some KPIs in first sheet

Second sheet which is mapping sheet says What KPI is require and what are the new KPI need to calculate at run time.

Reason for not to calculate the KPIs while uploading the data is, we need to calculate KPI for each region and Country level and it will replicate unnecessary data which will be huge....

I dont know how challenging it would be for you Qlik Expert people

KPI Mapping

how to use this mapping table

KPI 1 = (KPI 1 / 1) - 0

KPI 7 = (KPI 1 / (KPI 1 + KPI 2 + KPI 3) ) - 0

KPI 10 = ( (KPI 1 + KP 5 + KPI 6) / 1 ) - 0

KPI 11 = ( ( KPI 1 + KPI 2) / 1 ) - (KPI 3 + KP 4)

RequireKPIs	NumeratorKPI	DenominatorKPI	SubstractionKPI
KPI 1	KPI 1	1	0
KPI 2	KPI 2	1	0
KPI 3	KPI 3	1	0
KPI 4	KPI 4	1	0
KPI 5	KPI 5	1	0
KPI 6	KPI 6	1	0
KPI 7	KPI 1	KPI 1	0
KPI 7	KPI 1	KPI 2	0
KPI 7	KPI 1	KPI 3	0
KPI 8	KPI 2	KPI 1	0
KPI 8	KPI 2	KPI 2	0
KPI 8	KPI 2	KPI 3	0
KPI 9	KPI 3	KPI 1	0
KPI 9	KPI 3	KPI 2	0
KPI 9	KPI 3	KPI 3	0
KPI 10	KPI 1	1	0
KPI 10	KPI 5	1	0
KPI 10	KPI 6	1	0
KPI 11	KPI 1	1	KPI 3
KPI 11	KPI 1	1	KPI 4
KPI 11	KPI 2	1	KPI 3
KPI 11	KPI 2	1	KPI 4

DataMonth	Region	Country	KPIName	Value
1-Feb-17	Region 1	Country 08	KPI 2	1400
1-Feb-17	Region 2	Country 03	KPI 4	2292
1-Feb-17	Region 2	Country 02	KPI 6	1778
1-Feb-17	Region 1	Country 06	KPI 6	1182
1-Feb-17	Region 1	Country 08	KPI 2	2270
1-Feb-17	Region 1	Country 12	KPI 3	1019
1-Feb-17	Region 3	Country 07	KPI 3	1206
1-Feb-17	Region 3	Country 11	KPI 4	1276
1-Feb-17	Region 3	Country 07	KPI 3	1217
1-Feb-17	Region 1	Country 12	KPI 1	1403
1-Feb-17	Region 2	Country 02	KPI 1	1356
1-Feb-17	Region 2	Country 03	KPI 3	2039
1-Mar-17	Region 2	Country 04	KPI 1	1798
1-Mar-17	Region 3	Country 11	KPI 1	1773
1-Mar-17	Region 2	Country 02	KPI 6	2312
1-Mar-17	Region 2	Country 10	KPI 6	1411
1-Mar-17	Region 2	Country 04	KPI 6	2438
1-Mar-17	Region 2	Country 03	KPI 2	1522
1-Mar-17	Region 1	Country 09	KPI 1	1850
1-Mar-17	Region 2	Country 03	KPI 6	1570
1-Mar-17	Region 1	Country 08	KPI 3	1160
1-Mar-17	Region 3	Country 07	KPI 5	2286
1-Mar-17	Region 2	Country 02	KPI 2	1154
1-Mar-17	Region 2	Country 02	KPI 5	531
1-Apr-17	Region 1	Country 05	KPI 6	651
1-Apr-17	Region 1	Country 08	KPI 2	1019
1-Apr-17	Region 3	Country 01	KPI 2	1625
1-Apr-17	Region 2	Country 03	KPI 2	2085
1-Apr-17	Region 1	Country 09	KPI 2	831
1-Apr-17	Region 3	Country 01	KPI 2	1528
1-Apr-17	Region 3	Country 11	KPI 2	1251
1-Apr-17	Region 2	Country 04	KPI 1	1944
1-Apr-17	Region 1	Country 12	KPI 1	1394
1-Apr-17	Region 1	Country 05	KPI 1	1958
1-Apr-17	Region 1	Country 09	KPI 3	1398
1-Apr-17	Region 1	Country 09	KPI 2	2199
1-May-17	Region 2	Country 10	KPI 4	1062
1-May-17	Region 1	Country 08	KPI 4	746
1-May-17	Region 1	Country 09	KPI 4	1217
1-May-17	Region 2	Country 10	KPI 6	1863
1-May-17	Region 2	Country 03	KPI 2	1224
1-May-17	Region 1	Country 12	KPI 5	1271
1-May-17	Region 2	Country 04	KPI 3	633
1-May-17	Region 2	Country 10	KPI 6	962
1-May-17	Region 1	Country 05	KPI 5	1961
1-May-17	Region 2	Country 10	KPI 3	2354
1-May-17	Region 1	Country 06	KPI 1	606
1-May-17	Region 2	Country 04	KPI 6	1360
1-Jun-17	Region 2	Country 10	KPI 6	2306
1-Jun-17	Region 1	Country 08	KPI 5	1647
1-Jun-17	Region 1	Country 12	KPI 4	913
1-Jun-17	Region 3	Country 07	KPI 6	791
1-Jun-17	Region 2	Country 02	KPI 2	1764
1-Jun-17	Region 1	Country 05	KPI 6	588
1-Jun-17	Region 2	Country 10	KPI 5	2354
1-Jun-17	Region 2	Country 02	KPI 4	1886
1-Jun-17	Region 2	Country 10	KPI 2	1384
1-Jun-17	Region 1	Country 09	KPI 3	517
1-Jun-17	Region 2	Country 03	KPI 5	725
1-Jun-17	Region 3	Country 01	KPI 1	2097
1-Jul-17	Region 1	Country 08	KPI 1	2243
1-Jul-17	Region 1	Country 06	KPI 3	1000
1-Jul-17	Region 1	Country 05	KPI 1	2213
1-Jul-17	Region 2	Country 04	KPI 1	1915
1-Jul-17	Region 1	Country 08	KPI 2	2071
1-Jul-17	Region 1	Country 09	KPI 2	688
1-Jul-17	Region 3	Country 01	KPI 6	896
1-Jul-17	Region 1	Country 12	KPI 2	2015
1-Jul-17	Region 3	Country 01	KPI 6	2004
1-Jul-17	Region 1	Country 05	KPI 3	1757
1-Jul-17	Region 1	Country 12	KPI 4	1346
1-Jul-17	Region 2	Country 03	KPI 6	1293
1-Aug-17	Region 3	Country 01	KPI 4	1942
1-Aug-17	Region 1	Country 09	KPI 1	2439
1-Aug-17	Region 1	Country 05	KPI 6	1298
1-Aug-17	Region 2	Country 02	KPI 1	1650
1-Aug-17	Region 1	Country 12	KPI 5	1101
1-Aug-17	Region 2	Country 10	KPI 5	1105
1-Aug-17	Region 2	Country 02	KPI 5	1212
1-Aug-17	Region 3	Country 11	KPI 3	535
1-Aug-17	Region 1	Country 09	KPI 2	1511
1-Aug-17	Region 1	Country 05	KPI 3	2396
1-Aug-17	Region 3	Country 01	KPI 2	2048
1-Aug-17	Region 1	Country 08	KPI 2	2469
1-Sep-17	Region 1	Country 09	KPI 3	1421
1-Sep-17	Region 3	Country 01	KPI 6	2051
1-Sep-17	Region 3	Country 07	KPI 1	914
1-Sep-17	Region 3	Country 11	KPI 4	1994
1-Sep-17	Region 1	Country 08	KPI 3	866
1-Sep-17	Region 1	Country 05	KPI 4	1219
1-Sep-17	Region 1	Country 05	KPI 6	939
1-Sep-17	Region 1	Country 09	KPI 6	2090
1-Sep-17	Region 2	Country 04	KPI 1	1911
1-Sep-17	Region 3	Country 01	KPI 1	2000
1-Sep-17	Region 3	Country 01	KPI 4	1980
1-Sep-17	Region 2	Country 03	KPI 2	2182
1-Oct-17	Region 1	Country 12	KPI 2	925
1-Oct-17	Region 2	Country 04	KPI 2	1185
1-Oct-17	Region 3	Country 07	KPI 1	2124
1-Oct-17	Region 2	Country 03	KPI 4	978
1-Oct-17	Region 2	Country 04	KPI 5	1529
1-Oct-17	Region 1	Country 08	KPI 6	1264
1-Oct-17	Region 3	Country 11	KPI 5	1774
1-Oct-17	Region 2	Country 02	KPI 1	1933
1-Oct-17	Region 1	Country 09	KPI 1	1848
1-Oct-17	Region 1	Country 09	KPI 5	1145
1-Oct-17	Region 1	Country 08	KPI 6	552
1-Oct-17	Region 2	Country 10	KPI 4	1203
1-Nov-17	Region 2	Country 02	KPI 2	612
1-Nov-17	Region 1	Country 12	KPI 1	2156
1-Nov-17	Region 2	Country 02	KPI 4	1361
1-Nov-17	Region 1	Country 06	KPI 3	978
1-Nov-17	Region 1	Country 08	KPI 1	2435
1-Nov-17	Region 1	Country 05	KPI 6	2169
1-Nov-17	Region 3	Country 07	KPI 2	2079
1-Nov-17	Region 2	Country 02	KPI 3	1372
1-Nov-17	Region 3	Country 07	KPI 5	1383
1-Nov-17	Region 1	Country 12	KPI 5	2022
1-Nov-17	Region 3	Country 01	KPI 4	1615
1-Nov-17	Region 2	Country 10	KPI 4	1640
1-Dec-17	Region 3	Country 11	KPI 5	698
1-Dec-17	Region 1	Country 09	KPI 6	569
1-Dec-17	Region 3	Country 01	KPI 1	613
1-Dec-17	Region 1	Country 05	KPI 5	2068
1-Dec-17	Region 3	Country 07	KPI 3	1664
1-Dec-17	Region 2	Country 10	KPI 5	1501
1-Dec-17	Region 3	Country 07	KPI 5	1288
1-Dec-17	Region 1	Country 06	KPI 1	818
1-Dec-17	Region 3	Country 11	KPI 6	1882
1-Dec-17	Region 1	Country 06	KPI 4	2208
1-Dec-17	Region 1	Country 06	KPI 6	940
1-Dec-17	Region 3	Country 01	KPI 4	1155
1-Jan-18	Region 2	Country 02	KPI 3	1030
1-Jan-18	Region 3	Country 11	KPI 1	2251
1-Jan-18	Region 1	Country 05	KPI 6	1027
1-Jan-18	Region 2	Country 10	KPI 5	1475
1-Jan-18	Region 1	Country 09	KPI 4	1354
1-Jan-18	Region 3	Country 01	KPI 5	2280
1-Jan-18	Region 1	Country 09	KPI 6	1064
1-Jan-18	Region 1	Country 08	KPI 6	1873
1-Jan-18	Region 1	Country 12	KPI 5	1034
1-Jan-18	Region 3	Country 11	KPI 2	752
1-Jan-18	Region 2	Country 10	KPI 2	746
1-Jan-18	Region 2	Country 02	KPI 2	725

I am also not aware if there is any good practice that we need to follow while posting query in forum

Re: Run time KPI Calculation is required

sunny_talwar — Fri, 16 Feb 2018 16:31:39 GMT

I am also not aware if there is any good practice that we need to follow while posting query in forum

I think you have done great, a lot of people don't even do half of what you have provided, so def. pat on your back for this.

Few questions

1) Mapping is also available as a file to be loaded?

2) What object are you looking to show the required information, do you think you can provide the expected output in numerical terms based on the sample you have provided?

Re: Run time KPI Calculation is required

Peter_Cammaert — Fri, 16 Feb 2018 16:46:10 GMT

I think it would be easier to not use a mapping table, but to stuff expressions to calculate the complex KPIs into variables, and base those expressions on existing KPI values (KPI1-KPI6)

If you want to keep a flexible configuration system, maybe you can store those expressions in a QVD or a text file that is loaded at the start of your script.

Re: Run time KPI Calculation is required

Peter_Cammaert — Fri, 16 Feb 2018 17:27:05 GMT

Big ugly screenshot:

topic Run time KPI Calculation is required in QlikView

Run time KPI Calculation is required

Re: Run time KPI Calculation is required

Re: Run time KPI Calculation is required

Re: Run time KPI Calculation is required