Aggregation to two dimensions

Question

Hi,I am looking for a PQL code to solve the following.We have data for multiple dimension over a period of time. I would like to get the data aggregated on one dimension per month.All the data is within one table.|Dim1|Dim2|Dim3| Month | KPI ||----|-----|-----|------|---|| A    | X1   | Y1   | Jan-21 | 300 || A    | X2   | Y2   | Jan-21 | 200 || A    | X3   | Y3   | Jan-21 | 400 || A    | X1   | Y1   | Feb-21 | 200 || A    | X2   | Y2   | Feb-21 | 100 || A    | X3   | Y3   | Feb-21 | 500 || B    | X1   | Y1   | Jan-21 | 300 || B    | X2   | Y2   | Jan-21 | 200 || B    | X3   | Y3   | Jan-21 | 400 || B    | X1   | Y1   | Feb-21 | 200 || B    | X2   | Y2   | Feb-21 | 300 || B    | X3   | Y3   | Feb-21 | 500 | What I would like to get is.jan-21feb-21diff.A900800-100B900100200 I have been trying with the domain table and PU functions but I was not able to make it work.Thank you

willi.mann · Accepted Answer

Hi,here, a domain table solution - just row-wise, not column wisedim1: PU_FIRST(DOMAIN_TABLE(month, Dim1), month)dim2: PU_FIRST(DOMAIN_TABLE(month, Dim1), Dim1)dim3: PU_SUM(DOMAIN_TABLE(month, Dim1), KPI)dim4: RUNNING_SUM(PU_SUM(DOMAIN_TABLE(month, Dim1), KPI), ORDER BY (month), PARTITION BY (Dim1))Not syntax-verified, but I hope it gives you the idea.Willi

pooja.tiwar11 · Answer

Hi Mel,Please try with the below logic.You need to take 3 dimensions asDim1 Table.Dim1Jan - SUM(CASE WHEN MONTH(Table.MONTH) = 1 THEN Table.KPI ELSE NULL END)Feb - SUM(CASE WHEN MONTH(Table.MONTH) = 2 THEN Table.KPI ELSE NULL END)And in KPIDifferenceSUB(SUM(CASE WHEN MONTH(Table.MONTH) = 2 THEN Table.KPI ELSE NULL END),SUM(CASE WHEN MONTH(Table.MONTH) = 1 THEN Table.KPI ELSE NULL END))Hope this will helps.Thanks,Pooja

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN