KPI's channel wise..Target Vs Actuals in Different Tab's Required

[pra_kale]

Hi All,

I am attaching one sample excel sheet of KPI's which contains KPI's of different channels with Month on Month Target vs Achievement.

I am attaching only few sheets, in reality there are almost 15 / 20 sheets with some around 30 KPI's.

Below given are my requirements:-

I) All sheets in that excel, i.e. from KPI's, file should get uploaded in one load statement...so will avoid work of uploading 15 / 20 sheets

II) In Qlikview channel wise separate sheets are required...means each sheet contains that channel data only like Banka, Alternate, Direct...etc. separately...same like given in excel.

III) Targets are already available for that year month wise but Actuals figs. are coming from the system i.e. by calculations. So for me difficult task is how to populate Actual values for different different channels under their KPI's only and also for that month..last months figs. will not change. Means April To July figs. are frozen so values should get populate in August month only.

Can any one help me to resolve this.

Thanks in advance.

[petter]

Is it something like this you had in mind?

[pra_kale]

Thanks for your reply..

But, I want Each channel KPI's in a separate sheet..like Agency channel KPI's in 1 sheet, Banka Channel KPI's in 2nd Sheet...like for other channels. Final out-put should look like the way I have given in excel.

Secondly, please also guide how I can populate Actuals values against Target of each channels and against their KPI's...As Targets are readily available but Actuals values are coming from back-end calculations...

Thanks in Advance

[petter]

Actually there are multiple ways of achieving different "TABs" ....

I have examplified two of the approaches in the attached app but I can think of at least three more approaches:

1) Single Pivot Table having a LIST BOX horisontally displayed with no caption and the only one selected option enabled.

2) Container object with six LIST BOXes with hardcoded Set Analysis to select the right Channel

3) Separate Sheets for each tab and still like option 2 six LIST BOXes and hardcoded Set Analysis

4) A variation of 1 - using buttons to create a button bar with six hardcoded buttons and using actions and variables to select the right Channel

5) A variation of 4 - using Text Boxes instead of buttons.

I clearly prefer #1 ...

See the first two options as screen shot underneath and find the App attached:

If you don't like the all green hightlighting of the selected channel in the LIST BOX you can select another "selection style" on the list box's Presentation Tab - Selection Style Override.

[petter · Accepted solution]

The last requirement would be better posed in a new question I think - as we need a better explanation on exactly how you want your source data to come in and how you want your Actual vs Target displayed and calculated...

[pra_kale]

Hi,

That's great This is exactly what I want..yes I too agree, single Pivot with horizontal list box is a better option...

But, just want to ask one more thing...at the start we have hard coded the sheet names, but whether it is possible by any means we can load all the sheets available in that excel once we mentioned the path..

Secondly, as suggested I will put second question continuation to this where I will mention how data will come in actuals..for reference I will not make any change in a subject line.

Thanks once again..

[petter]

Actually that is a bit of a problem with the XLSX (OOXML) format. Previously or the old XLS format you had that option. But for some strange reason Qlik didn't support that in their support for XLSX ...

[pra_kale]

Hello,

The application is working vary nicely

but, few more things I am required in the same application so it will become more helpful...can you help in it..

1) Can I get Channel wise Target Vs Actuals in a graphs in a separate sheet say Dashboard...where I will select channels available i.e. like Banka, Alternate, Direct..etc. and then I will select the KPI's available and accordingly month wise graphs generate...I have tried it but I am not get any out-put...also in second graph or if possible in same graph..If I select Two or more channels and KPI's then comparative graphs get generate..

2) In earlier sheet their are Three more columns which I forgot to give i.e. Total Target, Total Actuals and Measures..what happened is when I execute the application Only Two additional columns are get exported i.e. Total Target at the last and Measures at the same position. But Total  Actual columns not get exported...also I want excel sheet should get exported in the same order of rows and columns..what I observed is Gross Premium is at the top but Net Premium moved down also Total columns are moved at the end...

3)  Can I also get the Actuals figs. highlighted in green if Actuals are grater than Target and if not then it is in Red.

4) In pivot how I can get the scrolling option, as Pivot is currently fettling to the screen because of more KPI's and I can not scroll to know the last KPI result.

for reference I am attaching a new excel after including three additional columns.

Thanks in advance