Formula excel for PQL

I need some help guys, I want to add up the day's payments to generate an accrual. Example: Day 01 $100, Day 2 $200 Day 03 $300

In the amount I want, I would do it like this: on day 1, $100, on day 2, $300, on day 3, $600.

But I don't know how to do it, below in Excel how it was done.This needs to be per month. So it adds up only within the month when the month ends it resets and dnv starts. I don't know if it's clear, but you can try to clarify better.

Excel:

my attempt at PQL

SUM (

CASE WHEN "BSEG"."AUGDT" >="BSEG"."AUGDT" THEN KPI("valor_acumulado") + "BSEG"."DMBTR" ELSE

"BSEG"."DMBTR"

END

)

valor_acumulado = CASE WHEN

"BSEG"."AUGDT" >= "BSEG"."AUGDT" THEN "BSEG"."DMBTR" END

Page 1 / 1

Hi @douglas.henri,

I think you can solve this using RUNNING_TOTAL or RUNNING_SUM (not quite sure which is applicable in your case) and using the ROUND_MONTH operator to calculate the accrual for every month.

OLAP TABLE:

Dimension:

ROUND_MONTH("ACTIVITIES_TABLE_PO"."EVENT_TIME")

KPI:

RUNNING_TOTAL(SUM("CASE_TABLE_PO"."CUMULATIVE_NET_WORTH"))

FILTER:

FILTER YEAR("ACTIVITIES_TABLE_PO"."EVENT_TIME") = 2018

There are most likely smarter ways to go about this, but hopefully this gets you in the right track. 😊

Kind regards,

Sverre Klein

Cool, thanks Sverre, I'll test it. It already gave me better guidance on which function to use.

No problem! Please keep me up-to-date if you potentially solved it or need more help 😊

@douglas.henri

Instead of using the Activity Event time in the dimension, use some date column which is unique for each case and based on which date you want to generate the running sum. Else if you use event time then one DMBTR will be repeated in multiple months.

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