Example in the below picture is one of the scenarios:
1. Invoice is posted in Jan 2023 but paid in May 2023. Hence, the total open invoice amount should be accountable for not only Jan but also for feb, march and april.
Unfortunately, I get the Total open invoice amount accounted from the posting month followed by all the rest of the month in the data set.
My current approach:
- Round(Posting Date) column: RANGE_APPEND(((ROUND_MONTH("O2C_AR_BSEG"."CPUDT"))), '1M')
- Total RUNNING SUM column:
RUNNING_SUM( SUM(
CASE WHEN ROUND_MONTH("O2C_AR_BSEG"."CPUDT") <> ROUND_MONTH("O2C_AR_BSEG"."AUGDT")
THEN
(
CASE WHEN ISNULL("O2C_AR_BSEG"."AUGDT") = 0
AND ROUND_MONTH("O2C_AR_BSEG"."CPUDT") < ROUND_MONTH("O2C_AR_BSEG"."AUGDT")
THEN "O2C_AR_BSEG"."WRBTR_CONVERTED"
END
)
END
))
I know that I should set a start and end range to my PQL query: Round(Posting Date)
I tried different variations to it but I always get a data type error since timeline column (posting date) data type should match the start and end range data type.
I tried to solve it as below: But I get an error that '1M' step_size should be INT but it has to be a STRING here as I want step_size to be monthly.
Below are my tries for the start and end range:
Try 1: RANGE_APPEND (ROUND_MONTH("O2C_AR_BSEG"."posting_date_month_"), '1M', ROUND_MONTH( "O2C_AR_BSEG"."CPUDT" ), ROUND_MONTH("O2C_AR_BSEG"."AUGDT" ))
Error: 
Try 2: RANGE_APPEND(TO_INT(TO_STRING(ROUND_MONTH("O2C_AR_BSEG"."CPUDT"), FORMAT ('%Y-%m'))), '1M', MONTH ( "O2C_AR_BSEG"."CPUDT" ), MONTHS_BETWEEN ( "O2C_AR_BSEG"."CPUDT", "O2C_AR_BSEG"."AUGDT" ))
Error: RANGE_APPEND: step_size must be of type INT, but got ySTRING].
Any input is highly appreciated
best regards,
Vaishali