QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor

Run time KPI Calculation is required

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

3 Replies
Highlighted
MVP

Re: Run time KPI Calculation is required

 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?

Highlighted

Re: Run time KPI Calculation is required

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.

Highlighted

Re: Run time KPI Calculation is required

Big ugly screenshot: