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: 
Amit_B
Creator
Creator

Displaying KPIs by rating

Hi experts,

In our model there are 5 important KPIs, each one of them is presented as percentages and in a Gauge Chart. The model is constantly updated and the change in values can be extreme.
We want to dynamically display on the sheet only the 3 with the lower value, each in a separate Gauge Chart (or other object).

For example,
The values are: kpi1=75%, kpi2=79%, kpi3=54%, kpi4=81%, kpi5=64%
There will be 3 Gauge Charts on the sheet - the first one will display kpi1, the second one will display kpi3 and the third one will display kpi5.

How can this be done?

Thanks.
Amit.

Labels (5)
7 Replies
JHuis
Creator III
Creator III

This is possible, only problem is without a sample data set it is tough. 

Can you maybe give a example data set?

Amit_B
Creator
Creator
Author

Thanks for the reply but I'm not sure how that would help...
There is not that much connection between the KPIs, each one of them measures something different.

This is a digital store.
The first KPI measures the percentage of products sold out of all products.

Item Code Item Name Extra Data
111 aaa ...
222 bbb ...
333 ccc ...

 

Order ID Order Date Time ... Item Code Units ...
1001     222 2  
1002     222 1  
1002     333 2  
1003     333 4  


kpi1 = 2/3 = 67% (only 2 items were sold from a pool of 3 items)

Additional KPIs refer to customers, different amounts of money, satisfaction, etc.

Gabbar
Specialist
Specialist

Lets Say there are two Column KPI and percentage,
Now you can Use rank Function To Rank KPIs on the basis of the percentage or if require another measure in the order(Ascending or descending) you want.
After that you can use your measure using 
Sum({<Dimension={"=('RankCalculatedFunction') = 'n')"}>}Dimension)  where n can be 1,2,3 On the basis of your requirement.

If anymore query please add a sample data set and the required output

Amit_B
Creator
Creator
Author

Great, I thought it should be done in a script, but how can I create such a table that the percentage/value column is calculated separately for each row and is based on data from other tables?
Would it be correct to use variables?
I would love to receive an example.

Gabbar
Specialist
Specialist

Could you provide a sample Data for your case, which contains enough data to calculate because the previous data set you gave wasnt enough to do the calculation.

Amit_B
Creator
Creator
Author

Let's take a simple example because the big problem for me is to create the KPIs table.
Let's say there are 2 tables, the first table is the stock and the second table is the sales table. There are 2 products and we need to calculate the percentage of items sold from each product, and to show the lower KPI value.

ProductID ProductName CurrentStock
001 A 10
002 B 5

 

OrderID ProductID QuantitySold
00001 A 3
00002 A 2
00002 B 1
00003 B 2


So,
KPI_A = (3+2) / 10 = 0.5 = 50% ---> Rank 2 ----> to show in the chart.
KPI_B = (1+2) / 5 = 0.6 = 60% ---> Rank 1

Thanks alot!

Ahidhar
Creator III
Creator III

try this 

tab2:
mapping load
ProductID,max(QuantitySold) as maxsold
resident tab1 group by ProductID;

tab:
load ProductID,ProductName,CurrentStock,ApplyMap('tab2',ProductID)/CurrentStock as kpi;

 

and in gauge chart

min(kpi)*100