Dear Community,

does anyone have any idea on how to create a chart in a view with running sum logic, that means for:

January 24: i would like to display the sum of purchase order value of the last 12 months from Jan 23 till end December 23

February 24: the sum of purchase order value (for example) from February 23 till end january 24

March 24: the sum of purchase order value ((for example) from March 23 till end February 24

and so on for the coming months.

I can imagine that i have to build the logic in the KPI itself.

Furthermore, i would like also to create a chart in a view with YTD Logic:

January 24: Data, like purchase order value, just from January 24

February 24: Sum of purchase order value of January & February 24

March 24: Sum of purchase order value of January & February & March 24

...

Thank you so much!

Page 1 / 1

Hello,

Regarding your first request, create a chart with X-axis KPI (month) and Y-axis KPI (Moving sum of purchase order value).

X-axis KPI (month): TO_STRING ( RANGE_APPEND ( ROUND_MONTH ("TABLE1"."DATE" ) , '1M' ), FORMAT ( '%Y-%m' ))
Y-axis KPI (Moving sum of purchase order value): MOVING_SUM(MAX(RUNNING_SUM ( "TABLE1"."NET ORDER VALUE", ORDER BY ( "TABLE1"."DATE"), PARTITION BY ( ROUND_MONTH ("TABLE1"."DATE")) )),-12,-1)

Feel free to use the following example with dummy data as an example of the end Result:Regarding your second request for a cumulative sum, create a chart with X-axis KPI (month) and Y-axis KPI (Cumulative sum of purchase order value).

X-axis KPI (month): TO_STRING ( RANGE_APPEND ( ROUND_MONTH ("TABLE1"."DATE" ) , '1M' ), FORMAT ( '%Y-%m' ))

Y-axis KPI (Cumulative sum of purchase order value):(RUNNING_SUM ( MAX(RUNNING_SUM ( "TABLE1"."NET ORDER VALUE", ORDER BY ( "TABLE1"."DATE"), PARTITION BY ( ROUND_MONTH ("TABLE1"."DATE")) ))))

Hello,

Regarding your first request, create a chart with X-axis KPI (month) and Y-axis KPI (Moving sum of purchase order value).

X-axis KPI (month): TO_STRING ( RANGE_APPEND ( ROUND_MONTH ("TABLE1"."DATE" ) , '1M' ), FORMAT ( '%Y-%m' ))
Y-axis KPI (Moving sum of purchase order value): MOVING_SUM(MAX(RUNNING_SUM ( "TABLE1"."NET ORDER VALUE", ORDER BY ( "TABLE1"."DATE"), PARTITION BY ( ROUND_MONTH ("TABLE1"."DATE")) )),-12,-1)

Feel free to use the following example with dummy data as an example of the end Result:Regarding your second request for a cumulative sum, create a chart with X-axis KPI (month) and Y-axis KPI (Cumulative sum of purchase order value).

X-axis KPI (month): TO_STRING ( RANGE_APPEND ( ROUND_MONTH ("TABLE1"."DATE" ) , '1M' ), FORMAT ( '%Y-%m' ))

Y-axis KPI (Cumulative sum of purchase order value):(RUNNING_SUM ( MAX(RUNNING_SUM ( "TABLE1"."NET ORDER VALUE", ORDER BY ( "TABLE1"."DATE"), PARTITION BY ( ROUND_MONTH ("TABLE1"."DATE")) ))))

Hello,

Thanks a lot for your answer. It is very helpful.

Best regards,

Mostafa

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