Last 12 months

[Zaga_69]

Hi everyone!

I need to calculate the last twelve months for the following formula:

Sum({<DOT_STATUS={'Too Early'},PlannedGoodsMovPeriod={"$(=max(YearMonth))"}>}YCOUNT)

I have tried using addmonths function in the formula, but ii does not work.

Any hint?

Thankns

Edi

[Zaga_69]

Totally agree with you...I think it is good start, however definitely I will need to change it as soon as possible to avoid potentials issues in the future as you have mentioned...

Going back to your recommendations I will try the following steps: 

1. 

Date(Date#(YWADAT, 'YYYYMMDD'), 'YYYYMM') as PlannedGoodsMovPeriod, 
Date(Date#("0DATE", 'YYYYMMDD'), 'YYYYMM') as ActualGoodsMovPeriod,

2. 

Date(MonthStart(TempDate), 'YYYYMM') as YearMonth,

3. 

Sum({<DOT_STATUS = {'Too Early'}, PlannedGoodsMovPeriod = {"$(='>=' & Date(AddMonths(Max(YearMonth), -12), 'YYYYMM) & '<=' & Date(Max(YearMonth), 'YYYYMM'))"}>} YCOUNT)

I will let you know if it works...

Again thank you for helping me. I really appreciate that.

Best regards,

Edi

[sunny_talwar]

I am not sure if you want sum of last 12 months or just get the value from 12 months ago... if you need last 12 months... then this looks good

Sum({<
 DOT_STATUS = {'Too Early'},
 PlannedGoodsMovPeriod = {"$(='>=' & Date(AddMonths(Max(YearMonth), -12), 'YYYYMM) & '<=' & Date(Max(YearMonth), 'YYYYMM'))"}
>} YCOUNT)

For 12 month ago... try this

Sum({<
 DOT_STATUS = {'Too Early'},
 PlannedGoodsMovPeriod = {"$(=Date(AddMonths(Max(YearMonth), -12), 'YYYYMM)"}
>} YCOUNT)

This should be similar to this expression you mentioned

Sum({<DOT_STATUS={'Too Early'}, PlannedGoodsMovPeriod={"$(=max(YearMonth)-100)"}>}YCOUNT)