Filtering Graph on 2 KPI's with Filters

Hi All,

I’m trying to implement a bar graph between two of the same KPI’s but with different filters on them. I’ve created the bar chart with both KPI’s displaying by month, and then I added two dropdown buttons that select the plant/warehouse of interest. Both of these dropdown buttons’ values are then set to text variables I created.

I want to now filter each KPI bar segment based on the button values. So the end result would be two bars for each month, with one bar being filtered on the first button’s value and the second bar being filtered on the second button’s value.

Below is what I have now, just without the functionality of the filters to distinguish each bar.

Any help is greatly appreciated. Thanks.

Hi msthil2,

thank you for your question.
To solve this you will need to incorporate that filter statement (which is using the variable) in the KPI calculations.
Depending on what kind of KPI you are calculating here, you could achieve this either with a pull-function that is allowing you to define a filter statement {e.g. PU_SUM ( child_table, parent_table.column [, filter_expression] ) }, or also a nested CASE WHEN statement inside your aggregation function {something like SUM(CASE WHEN table.column = <%=variable1%> THEN 1.0 ELSE 0.0 END)

Hope that helps.
All the best, Sabeth

Hi Sabeth,

Thanks for the quick reply. I tried both of these methods and cannot seem to get it working. I’m using a fairly complex (at least to my eyes) KPI and not sure exactly how to enact the two solutions you provided. My KPI is:

AVG(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Create Delivery’] TO LAST_OCCURRENCE[‘Record Goods Issue’], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".“EVENTTIME”, DAYS)))

I’m calculating the average throughput time between a delivery created and a goods issued in days. If using PU_AVG functions, would this entire query above be my parent table and the column to filter on be my child? Not sure where to add what where.

Thanks again for all the help.

Hi @msthil2

I can see from your dropdowns that you want to filter your KPI based on a Sales Org - so I’m assuming your filter should go on the VBAK table? If this is the case, you can just put a CASE WHEN around the CALC_THROUGHPUT:

AVG(
CASE WHEN "VBAK"."VKORG" = '<%=YOUR_VARIABLE%>' THEN 
CALC_THROUGHPUT(FIRST_OCCURRENCE['Create Delivery'] TO LAST_OCCURRENCE['Record Goods Issue'], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES"."EVENTTIME", DAYS)) 
ELSE NULL END
)

Best
David

Worked like a charm! Thanks Sabeth and David. You guys are life savers.

1 Like