Creating a KPI

[#1]

Hi

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?

[#2 · Accepted solution]

I guess I just found the answer myself.

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

[eduardo_sommer]

Try using set analysis:

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

Eduardo

[#4]

Oh, that seems to do the trick

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

[eduardo_sommer]

Did it get what you expected?

Eduardo

[#6]

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?

[eduardo_sommer]

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

[#8]

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).

[eduardo_sommer]

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

Eduardo