Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
lisabarelle
Contributor
Contributor

KPI to show relative amount, but without using set analysis

I'm trying to create KPIs to show relative amounts, but without using set analysis.

I have a measure called [RAG] with 3 possible entries: Red, Amber or Green. I'm trying to sum the [VALUE] for each possible entry and show it has a KPIs. I've tried with set analysis, like this:

KPI 1: =sum({< [RAG]={'Red'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

KPI 2: =sum({< [RAG]={'Amber'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

KPI 3: =sum({< [RAG]={'Green'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

This works as long as I don't select a subset. In other words, the relative amounts are correct to start with, but if I then select the subset 'Green', KPI 3 equals 100% (expected), but KPI 1 and 2 don't go to 0 (which I am trying to get).

I can get the proper (expected) behavior using a stacked barchart  ([RAG] as bars, sum([VALUE])/sum(TOTAL [VALUE]) as measure] and displaying the value, but I can't get it as a simple KPI/ number. I'm thinking this is because I use set analysis, but not really sure. I'm also convinced there must be a dumb simple way to do this, but I can't figure out how.

Thanks

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try adjusting  your set analysis as I did below.

KPI 1: =sum({< [RAG]*={'Red'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

 

KPI 2: =sum({< [RAG]*={'Amber'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

 

KPI 3: =sum({< [RAG]*={'Green'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

View solution in original post

3 Replies
Vegar
MVP
MVP

Try adjusting  your set analysis as I did below.

KPI 1: =sum({< [RAG]*={'Red'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

 

KPI 2: =sum({< [RAG]*={'Amber'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

 

KPI 3: =sum({< [RAG]*={'Green'}>}[VALUE]) / sum(TOTAL <PROGRAM> [VALUE])

lisabarelle
Contributor
Contributor
Author

This is pure genius! 

Could you explain what the * does so I also learn how to use it elsewhere.

And is there an easy way to make sure a KPI never changes (independent of any other selection?)

Thanks!

Vegar
MVP
MVP

{<[RAG]*={'Green'}>} is short for {<[RAG]=RAG * {'Green'}>}

 

The * is used to get the union of the selected RAG values and the value 'Green'.