Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-02-15 08:34 AM

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)


RequireKPIsNumeratorKPIDenominatorKPISubstractionKPI
KPI 1KPI 110
KPI 2KPI 210
KPI 3KPI 310
KPI 4KPI 410
KPI 5KPI 510
KPI 6KPI 610
KPI 7KPI 1KPI 10
KPI 7KPI 1KPI 20
KPI 7KPI 1KPI 30
KPI 8KPI 2KPI 10
KPI 8KPI 2KPI 20
KPI 8KPI 2KPI 30
KPI 9KPI 3KPI 10
KPI 9KPI 3KPI 20
KPI 9KPI 3KPI 30
KPI 10KPI 110
KPI 10KPI 510
KPI 10KPI 610
KPI 11KPI 1 1KPI 3
KPI 11KPI 1 1KPI 4
KPI 11KPI 2 1KPI 3
KPI 11KPI 2 1KPI 4


   

DataMonthRegionCountryKPINameValue
1-Feb-17Region 1Country 08KPI 21400
1-Feb-17Region 2Country 03KPI 42292
1-Feb-17Region 2Country 02KPI 61778
1-Feb-17Region 1Country 06KPI 61182
1-Feb-17Region 1Country 08KPI 22270
1-Feb-17Region 1Country 12KPI 31019
1-Feb-17Region 3Country 07KPI 31206
1-Feb-17Region 3Country 11KPI 41276
1-Feb-17Region 3Country 07KPI 31217
1-Feb-17Region 1Country 12KPI 11403
1-Feb-17Region 2Country 02KPI 11356
1-Feb-17Region 2Country 03KPI 32039
1-Mar-17Region 2Country 04KPI 11798
1-Mar-17Region 3Country 11KPI 11773
1-Mar-17Region 2Country 02KPI 62312
1-Mar-17Region 2Country 10KPI 61411
1-Mar-17Region 2Country 04KPI 62438
1-Mar-17Region 2Country 03KPI 21522
1-Mar-17Region 1Country 09KPI 11850
1-Mar-17Region 2Country 03KPI 61570
1-Mar-17Region 1Country 08KPI 31160
1-Mar-17Region 3Country 07KPI 52286
1-Mar-17Region 2Country 02KPI 21154
1-Mar-17Region 2Country 02KPI 5531
1-Apr-17Region 1Country 05KPI 6651
1-Apr-17Region 1Country 08KPI 21019
1-Apr-17Region 3Country 01KPI 21625
1-Apr-17Region 2Country 03KPI 22085
1-Apr-17Region 1Country 09KPI 2831
1-Apr-17Region 3Country 01KPI 21528
1-Apr-17Region 3Country 11KPI 21251
1-Apr-17Region 2Country 04KPI 11944
1-Apr-17Region 1Country 12KPI 11394
1-Apr-17Region 1Country 05KPI 11958
1-Apr-17Region 1Country 09KPI 31398
1-Apr-17Region 1Country 09KPI 22199
1-May-17Region 2Country 10KPI 41062
1-May-17Region 1Country 08KPI 4746
1-May-17Region 1Country 09KPI 41217
1-May-17Region 2Country 10KPI 61863
1-May-17Region 2Country 03KPI 21224
1-May-17Region 1Country 12KPI 51271
1-May-17Region 2Country 04KPI 3633
1-May-17Region 2Country 10KPI 6962
1-May-17Region 1Country 05KPI 51961
1-May-17Region 2Country 10KPI 32354
1-May-17Region 1Country 06KPI 1606
1-May-17Region 2Country 04KPI 61360
1-Jun-17Region 2Country 10KPI 62306
1-Jun-17Region 1Country 08KPI 51647
1-Jun-17Region 1Country 12KPI 4913
1-Jun-17Region 3Country 07KPI 6791
1-Jun-17Region 2Country 02KPI 21764
1-Jun-17Region 1Country 05KPI 6588
1-Jun-17Region 2Country 10KPI 52354
1-Jun-17Region 2Country 02KPI 41886
1-Jun-17Region 2Country 10KPI 21384
1-Jun-17Region 1Country 09KPI 3517
1-Jun-17Region 2Country 03KPI 5725
1-Jun-17Region 3Country 01KPI 12097
1-Jul-17Region 1Country 08KPI 12243
1-Jul-17Region 1Country 06KPI 31000
1-Jul-17Region 1Country 05KPI 12213
1-Jul-17Region 2Country 04KPI 11915
1-Jul-17Region 1Country 08KPI 22071
1-Jul-17Region 1Country 09KPI 2688
1-Jul-17Region 3Country 01KPI 6896
1-Jul-17Region 1Country 12KPI 22015
1-Jul-17Region 3Country 01KPI 62004
1-Jul-17Region 1Country 05KPI 31757
1-Jul-17Region 1Country 12KPI 41346
1-Jul-17Region 2Country 03KPI 61293
1-Aug-17Region 3Country 01KPI 41942
1-Aug-17Region 1Country 09KPI 12439
1-Aug-17Region 1Country 05KPI 61298
1-Aug-17Region 2Country 02KPI 11650
1-Aug-17Region 1Country 12KPI 51101
1-Aug-17Region 2Country 10KPI 51105
1-Aug-17Region 2Country 02KPI 51212
1-Aug-17Region 3Country 11KPI 3535
1-Aug-17Region 1Country 09KPI 21511
1-Aug-17Region 1Country 05KPI 32396
1-Aug-17Region 3Country 01KPI 22048
1-Aug-17Region 1Country 08KPI 22469
1-Sep-17Region 1Country 09KPI 31421
1-Sep-17Region 3Country 01KPI 62051
1-Sep-17Region 3Country 07KPI 1914
1-Sep-17Region 3Country 11KPI 41994
1-Sep-17Region 1Country 08KPI 3866
1-Sep-17Region 1Country 05KPI 41219
1-Sep-17Region 1Country 05KPI 6939
1-Sep-17Region 1Country 09KPI 62090
1-Sep-17Region 2Country 04KPI 11911
1-Sep-17Region 3Country 01KPI 12000
1-Sep-17Region 3Country 01KPI 41980
1-Sep-17Region 2Country 03KPI 22182
1-Oct-17Region 1Country 12KPI 2925
1-Oct-17Region 2Country 04KPI 21185
1-Oct-17Region 3Country 07KPI 12124
1-Oct-17Region 2Country 03KPI 4978
1-Oct-17Region 2Country 04KPI 51529
1-Oct-17Region 1Country 08KPI 61264
1-Oct-17Region 3Country 11KPI 51774
1-Oct-17Region 2Country 02KPI 11933
1-Oct-17Region 1Country 09KPI 11848
1-Oct-17Region 1Country 09KPI 51145
1-Oct-17Region 1Country 08KPI 6552
1-Oct-17Region 2Country 10KPI 41203
1-Nov-17Region 2Country 02KPI 2612
1-Nov-17Region 1Country 12KPI 12156
1-Nov-17Region 2Country 02KPI 41361
1-Nov-17Region 1Country 06KPI 3978
1-Nov-17Region 1Country 08KPI 12435
1-Nov-17Region 1Country 05KPI 62169
1-Nov-17Region 3Country 07KPI 22079
1-Nov-17Region 2Country 02KPI 31372
1-Nov-17Region 3Country 07KPI 51383
1-Nov-17Region 1Country 12KPI 52022
1-Nov-17Region 3Country 01KPI 41615
1-Nov-17Region 2Country 10KPI 41640
1-Dec-17Region 3Country 11KPI 5698
1-Dec-17Region 1Country 09KPI 6569
1-Dec-17Region 3Country 01KPI 1613
1-Dec-17Region 1Country 05KPI 52068
1-Dec-17Region 3Country 07KPI 31664
1-Dec-17Region 2Country 10KPI 51501
1-Dec-17Region 3Country 07KPI 51288
1-Dec-17Region 1Country 06KPI 1818
1-Dec-17Region 3Country 11KPI 61882
1-Dec-17Region 1Country 06KPI 42208
1-Dec-17Region 1Country 06KPI 6940
1-Dec-17Region 3Country 01KPI 41155
1-Jan-18Region 2Country 02KPI 31030
1-Jan-18Region 3Country 11KPI 12251
1-Jan-18Region 1Country 05KPI 61027
1-Jan-18Region 2Country 10KPI 51475
1-Jan-18Region 1Country 09KPI 41354
1-Jan-18Region 3Country 01KPI 52280
1-Jan-18Region 1Country 09KPI 61064
1-Jan-18Region 1Country 08KPI 61873
1-Jan-18Region 1Country 12KPI 51034
1-Jan-18Region 3Country 11KPI 2752
1-Jan-18Region 2Country 10KPI 2746
1-Jan-18Region 2Country 02KPI 2725

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

387 Views
0 Likes
3 Replies
sunny_talwar
MVP
MVP
‎2018-02-16 11:31 AM

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?

299 Views
0 Likes
Peter_Cammaert
Partner - Champion III
Partner - Champion III
‎2018-02-16 11:46 AM

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.

299 Views
0 Likes
Peter_Cammaert
Partner - Champion III
Partner - Champion III
‎2018-02-16 12:27 PM

In response to Peter_Cammaert

Big ugly screenshot:

Run-time KPI calculation thread291539.jpg

299 Views