Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Creating a KPI

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?

1 Solution

Accepted Solutions
Highlighted
Not applicable

I guess I just found the answer myself.

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

View solution in original post

8 Replies
Highlighted
Not applicable

I guess I just found the answer myself.

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

View solution in original post

Highlighted
Partner
Partner

Try using set analysis:

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

Eduardo

Highlighted
Not applicable

Oh, that seems to do the trick

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

Partner
Partner

Did it get what you expected?

Eduardo

Highlighted
Not applicable

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?

Highlighted
Partner
Partner

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

Highlighted
Not applicable

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

Highlighted
Partner
Partner

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

Eduardo