Calculate Euro value but only for the past 6 months (moving date)

Hi Community,

I am building an OLAP table and I would like to calculate the average value over the past 6 months. With the 6 month time frame changing as time go on.

CASE WHEN ROUND_MONTH(ADD_MONTHS(“CASES”.“PostingDate”, -6)) THEN TRIMMED_MEAN(“CASES”.“Value(EUR)”, 5, 5) ELSE NULL END

I have tried various options of the above and cant get it to work. Can you please help?

Thanks,
Anna

Hi Anna,

there are 2 issues in the formula which are causing errors.

  1. You would need an operator for your condition ROUND_MONTH(ADD_MONTHS(“CASES”.“PostingDate”, -6))
    To get data from the last 6 months, you could use following formula which uses the operator >= to consider only data from last 6 months.
    ROUND_MONTH("BKPF"."PostingDate") >= ROUND_MONTH(ADD_MONTHS("BKPF"."PostingDate", -6))

  2. When using the CASE WHEN you would rather return unaggregated data and aggregate the results afterwards:

     TRIMMED_MEAN(
     CASE 
     WHEN 
     ROUND_MONTH("CASES"."PostingDate") >= ROUND_MONTH(ADD_MONTHS("CASES"."PostingDate", -6))
     THEN "CASES"."Value(EUR)"
     ELSE NULL 
     END, 5, 5) 
    

Best,
Viana

Hi Viana,

Thank you for your response.

I have used the formula that you suggested however, i dont believe it is giving the desired results.

image

I am using your formula for euro value (6 month) column and the below formula for the trimmed value.

TRIMMED_MEAN(“CASES”.“Value(EUR)”, 5, 5)

Both are displaying the same results. I would anticipate that if i choose a posting date that is outside the past 6 months, then the formula you have suggested, would produce a zero value. Since we only want to calculate the value within the past 6 months of today’s calendar date.

Please let me know.

Hope youre staying healthy.

Cheers,
Anna

Hi Viana,

Hope all is going well and you are staying healthy.

Wanted to check in with you to see if you you had any updates on this outstanding question.

Thanks!
Anna