topic Different YTD calculation method in QlikView

Different YTD calculation method

Tue, 12 May 2009 20:54:50 GMT

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

Different YTD calculation method

Tue, 12 May 2009 21:05:18 GMT

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.

Different YTD calculation method

Tue, 12 May 2009 21:59:45 GMT

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?

Different YTD calculation method

Tue, 12 May 2009 22:17:15 GMT

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.

Different YTD calculation method

Tue, 12 May 2009 22:23:52 GMT

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

Different YTD calculation method

Tue, 12 May 2009 22:51:14 GMT

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.

Re: Different YTD calculation method

Fri, 02 Dec 2011 17:57:05 GMT

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

Different YTD calculation method

Mon, 05 Dec 2011 08:28:08 GMT

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