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