26 Replies Latest reply: May 13, 2016 7:44 AM by Umamaheswara Reddy

# Max(Month) not working in Set Analysis

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

• ###### Re: Max(Month) not working in Set Analysis

Can you create a new field in the script like this:

Num(Month(Date)) as MonthNum

and use that in your set analysis?

Sum({<MonthNum = {\$(=Max(MonthNum))}>}sales)

UPDATE: Also check if Max(Month) in text box gives you 3. If month only is month and doesn't contain Year and you have more than 1 years you might get a 12 in which case the set analysis will give a different result compared to Mar.

• ###### Re: Max(Month) not working in Set Analysis

Alternatively, you can try this also:

Sum({<MonthNum = {\$(=MaxString(MonthNum))}>}sales)

• ###### Re: Max(Month) not working in Set Analysis

Hi Sunny,

It's working.

Thanks alot

• ###### Re: Max(Month) not working in Set Analysis

Hi Sunny, Can this logic works for before last month

Ex: For Feb

Sum({1<Month={\$(=MaxString(Month)-1)}>}sales)

For Jan

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

• ###### Re: Max(Month) not working in Set Analysis

I tried these but not works

• ###### Re: Max(Month) not working in Set Analysis

just create the Month Year field in script using

=monthname(pl_date) as MonthYear

then you get the all expression

Current Month

Sum({1<MonthYear={"\$(=monthname(max(MonthYear)))"}>}sales)

Ex: For Feb

For Jan

• ###### Re: Max(Month) not working in Set Analysis

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

• ###### Re: Max(Month) not working in Set Analysis

May be this:

Feb:

Jan

• ###### Re: Max(Month) not working in Set Analysis

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

• ###### Re: Max(Month) not working in Set Analysis

If you compare this

vs.

in a text box, you will see a difference

• ###### Re: Max(Month) not working in Set Analysis

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

• ###### Re: Max(Month) not working in Set Analysis

Hi,

ok . Issue was in max() it convert into number and when we use addMonths()

then it again try to convert it into date which cause the issue.

Regards

• ###### Re: Max(Month) not working in Set Analysis

Precisely

• ###### Re: Max(Month) not working in Set Analysis

Hi,

We can simply use addmonths() on datefield

In his case

give better result.

Regards

• ###### Re: Max(Month) not working in Set Analysis

So essentially basing it of date field instead of Month field? That make sense

• ###### Re: Max(Month) not working in Set Analysis

Ya it's working Sunny

Thank you very much

• ###### Re: Max(Month) not working in Set Analysis

Hi,

try like

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

Regards

• ###### Re: Max(Month) not working in Set Analysis

Hi

or try

Regards

• ###### Re: Max(Month) not working in Set Analysis

Not working Max

• ###### Re: Max(Month) not working in Set Analysis

Hi,

which i have added in other suggestion.

check that work or not

Regards

• ###### Re: Max(Month) not working in Set Analysis

How you have created the Month in your script? if it is created using Month function in the script then this should work.

• ###### Re: Max(Month) not working in Set Analysis

I derived the month from date

Month(pl_date) as Month

• ###### Re: Max(Month) not working in Set Analysis

then try this

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

• ###### Re: Max(Month) not working in Set Analysis

Thanks you Guys for your un believeble support

Thanks

AR UMAMAHESH

• ###### Re: Max(Month) not working in Set Analysis

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

• ###### Re: Max(Month) not working in Set Analysis

Thank you guys

After all your support and help

I closed a big issue in my work

Thanks for your efforts and helping

Thanks,

AR UMAMAHESH