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

[sunny_talwar]

You have two different Date related field? PlannedGoodsMovPeriod and YearMonth? and you want to see last 12 months of PlannedGoodsMovPeriod by selecting in YearMonth? How are these two fields connected? What is the format for PlannedGoodsMovPeriod field?

[Zaga_69]

you want to see last 12 months of PlannedGoodsMovPeriod by selecting in YearMonth? 

Yes. I wanted to sum the field YCOUNT for the last 12 months (i.e YearMonth -12) 

Format: 

date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&''    AS PlannedGoodsMovPeriod, 

date(date#("0DATE",'YYYYMMDD'),'YYYYMM')&''   AS ActualGoodsMovPeriod,

The field YearMonth was created in the Calendar:

 Year(TempDate)&date(TempDate,'MM') As YearMonth,

Thanks in advance,

Edi

[sunny_talwar]

Out of curiosity why do you have & '' at end of PlannedGoodsMovPeriod and ActualGoodsMovPeriod? That converts a date field into text, is that something you really want to do for reason not mentioned in this thread?

[Zaga_69]

because it was the only solution I could found to calculate correctly the following formula:

+sum({<DOT_STATUS={"Too late"}, ActualGoodsMovPeriod={">$(=max(YearMonth))"}, PlannedGoodsMovPeriod={"<$(=max(YearMonth))"}, NotDeliveredYet={0} >} YCOUNT)

it is important to mention that the calendar is not connected with the data model. 

[sunny_talwar]

But why are you adding the part in red below?

date(date#(YWADAT,'YYYYMMDD'),'YYYYMM')&''    AS PlannedGoodsMovPeriod, 

[Zaga_69]

because I  need "201801" as a text

not as a number like 42918

[sunny_talwar]

But if you store the value as text, then how will you do numeric comparisons (the ones you are looking to do).

I think there is some misunderstanding here. Date is a dual function which can store your value as 201801 and have a numeric value 42918. By not changing it into text, you can use it as a numeric field for set analysis comparison and still show it as 201801.

Try this

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

and YearMonth like this

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

Now try this

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

[Zaga_69]

Hi, 

you can determine if one string as number is smaller than another one

201810 is greater than 201809

201810 still can be interpreted as number

and you have 1 number for 1 month

with your solution proposed, we will have a PlannedGoodsMov and ActualGoodsMov as an specific day and not a month and I need just sth like 201701..

[sunny_talwar]

You are right, and to fix that... change the code to this

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