topic Re: Current Month, Prior Month Flags in QlikView

Current Month, Prior Month Flags

satishkurra — Wed, 07 Mar 2018 16:41:30 GMT

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

Re: Current Month, Prior Month Flags

sunny_talwar — Wed, 07 Mar 2018 16:43:20 GMT

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

Re: Current Month, Prior Month Flags

YoussefBelloum — Wed, 07 Mar 2018 16:50:07 GMT

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

Re: Current Month, Prior Month Flags

isingh30 — Wed, 07 Mar 2018 16:50:42 GMT

Did you tried peek function?

Re: Current Month, Prior Month Flags

Gysbert_Wassenaar — Wed, 07 Mar 2018 16:51:43 GMT

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)

Re: Current Month, Prior Month Flags

satishkurra — Wed, 07 Mar 2018 17:05:01 GMT

Thanks

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