topic Re: Max(Month) not working in Set Analysis in QlikView

Max(Month) not working in Set Analysis

Fri, 13 May 2016 09:51:23 GMT

‌

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

sunny_talwar — Fri, 13 May 2016 09:53:08 GMT

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

sunny_talwar — Fri, 13 May 2016 09:54:19 GMT

Alternatively, you can try this also:

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

Re: Max(Month) not working in Set Analysis

Kushal_Chawda — Fri, 13 May 2016 10:00:26 GMT

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

Fri, 13 May 2016 10:02:34 GMT

I derived the month from date

Month(pl_date) as Month

Re: Max(Month) not working in Set Analysis

Kushal_Chawda — Fri, 13 May 2016 10:05:54 GMT

then try this

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

Re: Max(Month) not working in Set Analysis

Fri, 13 May 2016 10:07:09 GMT

Hi Sunny,

It's working.

Thanks for your immediate response

Thanks alot

Re: Max(Month) not working in Set Analysis

Fri, 13 May 2016 10:18:43 GMT

Thanks you Guys for your un believeble support

Thanks

AR UMAMAHESH

Re: Max(Month) not working in Set Analysis

Fri, 13 May 2016 10:36:49 GMT

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

Fri, 13 May 2016 10:38:01 GMT

I tried these but not works

Re: Max(Month) not working in Set Analysis

sunny_talwar — Fri, 13 May 2016 10:38:45 GMT

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

PrashantSangle — Fri, 13 May 2016 10:41:35 GMT

Hi,

try like

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

Regards

Re: Max(Month) not working in Set Analysis

PrashantSangle — Fri, 13 May 2016 10:44:39 GMT

or try

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

Regards

Re: Max(Month) not working in Set Analysis

Fri, 13 May 2016 10:44:55 GMT

Not working Max

Re: Max(Month) not working in Set Analysis

sunny_talwar — Fri, 13 May 2016 10:47:01 GMT

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)

Re: Max(Month) not working in Set Analysis

PrashantSangle — Fri, 13 May 2016 10:48:41 GMT

Hi,

I know i missed addmonths()

which i have added in other suggestion.

check that work or not

Regards

Re: Max(Month) not working in Set Analysis

swuehl — Fri, 13 May 2016 10:49:19 GMT

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

PrashantSangle — Fri, 13 May 2016 10:51:48 GMT

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

sunny_talwar — Fri, 13 May 2016 10:53:16 GMT

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

Re: Max(Month) not working in Set Analysis

sunny_talwar — Fri, 13 May 2016 10:56:54 GMT

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