Hi, @guilherme.luz11
Two ways to do it:
Create an Olap table, chose as dimention "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET", and as KPI SUM("_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY"). You'll get the sum totals for both cases (0 and 1).
Create a number card, and insert in the code editor:
SUM(CASE WHEN "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET"=0 THEN "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" ELSE 0 END)
Let me know if this was helpful.
See you.
Rui Bebiano
Hi @1460047871 ,
and thanks for your reply!
Sorry, I explained myself badly... I need to have that output but for each customer, in order to obtain the late deliveries per customer.
What I have tried:
- I created a OLAP table with "_APX_WDCRP_CASES_csv"."CUST_NAME" as a dimension;
- Added a component filter for cases where "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0
- Added SUM("_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY")
The problem is that using the component filter, it affects the other columns that I previously defined as KPI.
Is there any way of defining a filter for only this specific column?
Thanks for your help!
Guilherme Luz
Hi @1460047871 ,
and thanks for your reply!
Sorry, I explained myself badly... I need to have that output but for each customer, in order to obtain the late deliveries per customer.
What I have tried:
- I created a OLAP table with "_APX_WDCRP_CASES_csv"."CUST_NAME" as a dimension;
- Added a component filter for cases where "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0
- Added SUM("_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY")
The problem is that using the component filter, it affects the other columns that I previously defined as KPI.
Is there any way of defining a filter for only this specific column?
Thanks for your help!
Guilherme Luz
Ahh, I see.
Forget the filters, and try using this statement as KPI (the dimension is ok):
SUM(CASE WHEN "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0 THEN "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" ELSE 0 END)
Ahh, I see.
Forget the filters, and try using this statement as KPI (the dimension is ok):
SUM(CASE WHEN "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0 THEN "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" ELSE 0 END)
That statement really works, thank you! :)
However, I noticed that in "_APX_WDCRP_CASES_csv:
when "DELIVERED_DATE" = "PROMISED_DATE", the ORDER_DATE_MET = 1
(it is considered a late order, which in my opinion doesn't make sense because it complies with the promised date).
Due to that, I tried to write your statement in a different way but I was not succeeded:
SUM(CASE WHEN "_APX_WDCRP_CASES_csv"."DELIVERED_DATE" > "_APX_WDCRP_CASES_csv"."PROMISED_DATE" THEN "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" ELSE 0 END)
Do you have any idea of how to overcome this?
Thank you1
Ahh, I see.
Forget the filters, and try using this statement as KPI (the dimension is ok):
SUM(CASE WHEN "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0 THEN "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" ELSE 0 END)
Well, I'm not very familiar with date functions, but maybe the problem is that you cannot compare two dates in that way, using operators like ">".
Try using as condition (i.e., between "CASE WHEN" and "THEN"):
DAYS_BETWEEN ("_APX_WDCRP_CASES_csv"."DELIVERED_DATE" , "_APX_WDCRP_CASES_csv"."PROMISED_DATE")>0
I'm not sure about the syntax, but I hope it helps...
