Skip to main content
Solved

"SUM IF" in PQL language

  • August 30, 2021
  • 5 replies
  • 11 views
G
Level 3

Hi all,

 

I'm currently enrolling Celonis Summer School and I have no experience on PQL neither SQL languages, so I please ask your help.

 

I am trying to write something like a "SUM IF" expression. Basically, when column "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0, I want to sum "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" values.

 

I already tried to mix CASE WHEN function with SUM but I was not succeeded. I also searched for other functions related with aggregation but could not find one that applied.

 

Many thanks!

Guilherme Luz

Best answer by .146001171

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)

 

 

1 Attachments

5 replies

.
Level 6
  • .146001171
  • Level 6
  • August 30, 2021

Hi, @guilherme.luz11 

 

Two ways to do it:

 

  • OLAP TABLE

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).

 

  • NUMBER

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

G
Level 3

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

.
Level 6
  • .146001171
  • Level 6
  • Answer
  • August 31, 2021

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)

 

 

G
Level 3

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

 

 

1 Attachments
.
Level 6
  • .146001171
  • Level 6
  • August 31, 2021

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...

Community Code of ConductAccessibility statement