Hi All,
Im trying to implement a bar graph between two of the same KPIs but with different filters on them. Ive created the bar chart with both KPIs 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 buttons value and the second bar being filtered on the second buttons value.
Below is what I have now, just without the functionality of the filters to distinguish each bar.
Any help is greatly appreciated. Thanks.
image1811666 40.4 KB
Page 1 / 1
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
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. Im 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)))
Im 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.
Thanks for the quick reply. I tried both of these methods and cannot seem to get it working. Im 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)))
Im 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 Im 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:
Best
David
I can see from your dropdowns that you want to filter your KPI based on a Sales Org - so Im 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.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.