Different YTD calculation method

[#1]

Hi,

I'm relatively new at Qlikview so excuse my ignorance….

I have the following scenario.

Set of KPIs needs to be presented in a pivot chart using the desired output

KPI Name	Value (<Current Period>)	YTD
KPI A	345	456
KPI B	35,67	909
…	….	…

Pretty standard, no? well the problem is each KPI has a different property and require different calculation method of the YTD. KPI A ask for the aggregated average for the YTD, while KPI B require the aggregated Sum of the YTD.

Any ideas how to do this?

Dror

[#2]

How many different KPIs do you have? If there aren't too many, then you could probably use multiple if statements to handle it.

If(KPI='A', Avg(Agg), If(KPI='B', Sum(Agg)...

Could you handle it in the load script? It may be easier to create that table in your load script. Then, when formulas change, you only need to go back to the load script to edit it rather than having to look through multiple expressions.

This would be a great spot for user defined functions. Unfortunately, I don't think it's possible to create custom functions in QlikView. If anyone wants to start a petition to get user defined functions added to a future version, consider my name on it.

[#3]

Thanks for your quick respond.

Unfortunatly I have many KPIs and therefore, the formula needs to be define in external source at the script. Any Idea how I can do that?

[#4]

Could you use a union to select each KPI separately in the load script?

SQL Select 'A' As KPI, Sum(Agg) As YTD...

Union Select 'B' As KPI, Avg(Agg) As YTD...

You should be able to union up all of your KPIs into one table and then you could use the YTD field directly in any expressions.

[#5]

Unfortunatly I have 394 KPIs at the moment....and growing...

Is it possible to define the formula, let's say in excel file, load it as in-memory, bind it to the KPI table and calculate on the LOAD statement?

Dror

[#6]

Wow! A while back, I had a project that needed to automatically answer various regulation questions. It was built in Access. I created a table that stored the questions and in one field, I added a SQL statement that could be used to pull the answer to the question. Then I was able to use VB to create a union on the fly. I don't know that QlikView could handle that last piece.

It would probably be ideal to have a table with every KPI and the definition of that KPI. You could bring that into QlikView, but I don't know how you could take a string expression ("Sum(Agg)") and force QlikView to evaluate the string rather than just print it.

[#7 · Accepted solution]

You can create a table of expressions in excel/inline and read it into qlikview as a data island.

These can then be assigned to variables and use as expressions. (possibly other ways to do this)

see attached example.

Hope this helps,

Dipesh

[#8]

Yap. This is exactly what i was looking for. thanks!