topic Re: Creating a KPI in QlikView

Creating a KPI

Wed, 11 Jun 2014 01:46:56 GMT

I'm pretty new to Qlikview and struggle with creating a KPI.

I am trying to get a value in relation of another. I want to create an average personnel cost by dividing the total personnel cost (AC3 = 610) through the total employee (AccountCode = 96000). The formula looks like this:

Sum(If(AC3='610',Amount,0))/SUM(If(AccountCode='96000',Amount,0))

When I use the first part, I get proper values, when I only use the second part, I also get the proper values. But both parts together simply show nothing to display.

How can I make that division?

Re: Creating a KPI

Wed, 11 Jun 2014 01:53:29 GMT

I guess I just found the answer myself.

With creating two expressions and then use a 3rd calculate the quotient.

Re: Creating a KPI

eduardo_sommer — Wed, 11 Jun 2014 01:55:06 GMT

Try using set analysis:

Sum({$<AC3={'610'}>} Amount) / Sum({$<AccountCode={'96000'}>} Amount)

Eduardo

Re: Creating a KPI

Wed, 11 Jun 2014 02:18:59 GMT

Oh, that seems to do the trick

I guess I have to learn more on the power of the expressions...

Re: Creating a KPI

eduardo_sommer — Wed, 11 Jun 2014 02:45:03 GMT

Did it get what you expected?

Eduardo

Re: Creating a KPI

Wed, 11 Jun 2014 02:52:23 GMT

Yes, thank you very much.

Although I don't understand the formula yet completely.

I am just wondering, if I could get a stacked bar chart showing the same sums one level below the 610-Group. It would be at AC4. Do I have to create one expression for each sub-group or could I do that at once?

Re: Creating a KPI

eduardo_sommer — Wed, 11 Jun 2014 03:03:30 GMT

Hi Mathias

Set analysis is worth studying. It will very be useful in a lot of situations. Search for set analysis in the Resource area of the community. There is a lot of material there.

I didn't understand very well your problem with the stacked bar, but I think you will need another expression. Share more details of your application.

Eduardo

Re: Creating a KPI

Wed, 11 Jun 2014 04:07:10 GMT

I will certainly study that...

I have created this using 5 separate expressions

Salaries: Sum({$<AC4={'6110'}>} Amount) / Sum({$<AccountCode={'95010'}>} Amount)

Benefits: Sum({$<AC4={'6120'}>} Amount) / Sum({$<AccountCode={'95010'}>} Amount)

etc.

My question was, if I need to create 5 separate expressions or if I can do it in a single one using AC3 = '610' and Qlikview then displays the level below (AC4).

Re: Creating a KPI

eduardo_sommer — Wed, 11 Jun 2014 11:04:41 GMT

To answer this question, it's necessary to know your data model and, if possible, your qvw.

Eduardo