Current Month, Prior Month Flags

[satishkurra]

Hi

Currently i have the data as

Period, Amount

201711,100

201712,200

201801,300

I DO NOT have date is my data, it is already aggregated by Period, Month and Year only.

Based on this is there a way to dynamically get Current Month and Prior Month.

I'm using below expression, but this fails as my current month is Jan 2018 and Prior Month is Dec 2017. If both current month and prior month exists in same year it works. If not it fails.

=Sum({<Year = {"$(=Max(Year))"},Month = {"$(=Num(Max(Month)))"}>}Amount)

=Sum({<Year = {"$(=Max(Year))"},Month = {"$(=Num(Max(Month)-1))"}>}Amount)

Is there any dynamic way to get this worked every time

Note: I do not have the data as of current month to use TODAY function. Right now my current month is Jan 2018 and Prior Month is Dec 2017.

Please help

Thanks

Satish

[sunny_talwar]

Intead of using Month and Year separately, use a MonthYear field to do this

[YoussefBelloum]

As Sunny said here, use you original Period field, which is a YearMonth field, it will simplify your expression

[isingh30]

Did you tried peek function?

[Gysbert_Wassenaar · Accepted solution]

You can easily create a YearMonth date field with Date#(Period, 'YYYYMM') as YearMonth.

LOAD

    Period,

    Amount,

    Num(Date#(Period, 'YYYYMM')) as YearMonth

FROM

    ...source...

    ;

The you can use expressions like these:

=Sum({<YearMonth = {"$(=max(YearMonth)"}>}Amount)

=Sum({<YearMonth = {"$(=num(MonthStart(max(YearMonth),-1))"}>}Amount)

[satishkurra]

Thanks

Is there a way to write these 2 expressions to write as Flags in Edit Script and then use them in UI?