Max(Month) not working in Set Analysis

[#1]

‌

Hi All

I am facing Set Analysis issue in Qlikview 11.2 v

The issue is

I have to show the total sum in the set expression for the last month from date

Eg: I have data for some days ( the dates may be from Jan, Feb, Mar), i have to display the data based last month. As of now we got 3 months data in future there may be a chance to increase. I used static value in set expression like below, it worked, but when I place dynamic expression it's not work.

The below one works

Static: sum({<Month={"Mar"}>}sales)

The below one not working

Dynamic :

sum({<Month={$(=Max(Month))}>}sales)

Can any one help me to solve this

[sunny_talwar]

Try this:

Feb

Sum({1<Month={$(=MaxString(Month, 2))}>}sales)

Jan

Sum({1<Month={$(=MaxString(Month, 3))}>}sales)

My bad, these won't work. I am checking

[PrashantSangle]

Hi,

try like

sum({<Month={$(=Month(max(Month)-1))}>}sales)

Regards

[PrashantSangle]

Hi

or try

sum({<Month={$(=Month(AddMonths(max(Month),-1)))}>}sales)

Regards

[#5]

Not working Max

[sunny_talwar]

May be this:

Feb:

Sum({1<Month={$(=Month(AddMonths(Date#(MaxString(Month), 'MMM'), -1)))}>}sales)

Jan

Sum({1<Month={$(=Month(AddMonths(Date#(MaxString(Month), 'MMM'), -2)))}>}sales)

[PrashantSangle]

Hi,

I know i missed addmonths()

which i have added in other suggestion.

check that work or not

Regards

[swuehl]

Have a look at

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

on how to create set analysis to analyze certain points or periods in time.

It also explains in detail how you could create the fields in script necessary to make the set analysis work.

edit:

Also useful:

A Primer on Set Analysis

Data Types in QlikView

Dates in Set Analysis

[PrashantSangle]

Hi,

I think in one of his reply he told us that he using month() to evaluate month field.

so we don't need to use Date#().

Regards

[sunny_talwar]

If you compare this

=Month(AddMonths(Date#(MaxString(Month), 'MMM'), -1))

vs.

=Month(AddMonths(max(Month),-1))

in a text box, you will see a difference

[sunny_talwar]

Here you go

Not saying, the expression I provided is the best, but it seems to do the job.

Having said that, I really think the situation warrants for use of Num(Month(Date)) as MonthNum in script. This will make life so much easier for the OP

UPDATE: or a counter on Month-Year function would be ideal