topic Re: Set Expression Help in QlikView

Set Expression Help

abhaysingh — Tue, 28 Apr 2015 10:37:56 GMT

Hi ,

i have to show Max Month Sales, previous month sale and previous to previous month sales.

i created 3 variables

vMaxMonth = max(Month) /// month value are in number

vlastMonth = Max(Month)-1

vlast2lastMonth = Max(Month)-2

now i am using variable in this way.

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

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

please correct me it is not working

Re: Set Expression Help

Tue, 28 Apr 2015 10:42:49 GMT

Try this..

vMaxDate=max(Date)

vMaxMonth = month(vMaxDate)

vlastMonth =addmonths(vMaxDate,-1)

vlast2lastMonth =addmonths(vMaxDate,-2)

I assume there is a Date column

Re: Set Expression Help

abhaysingh — Tue, 28 Apr 2015 10:45:42 GMT

what would be the syntax??

Re: Set Expression Help

Tue, 28 Apr 2015 10:49:32 GMT

Let the expressions be same.. just change the variable definitions

Re: Set Expression Help

Tue, 28 Apr 2015 11:07:48 GMT

this worked:

vMonthMax: =max(month)

vMonthMax1: =max(month) - 1

vMonthMax2: =max(month) - 2

sum({<month = {$(vMonthMax)}>} sum)

sum({<month = {$(vMonthMax1)}>} sum)

sum({<month = {$(vMonthMax2)}>} sum)

Re: Set Expression Help

abhaysingh — Tue, 28 Apr 2015 11:08:58 GMT

max month working but last and last 2 last month not working

Re: Set Expression Help

abhaysingh — Tue, 28 Apr 2015 11:10:12 GMT

Alexandra, this is the same i was doing but not working.. not able to understand why

Re: Set Expression Help

Tue, 28 Apr 2015 11:13:51 GMT

can you share the sample qvw file? 'cos it worked for me perfectly

Re: Set Expression Help

Tue, 28 Apr 2015 11:19:54 GMT

Abhay,

I think if you change the definition of these variables to

vlastMonth =month(addmonths(vMaxDate,-1))

vlast2lastMonth =month(addmonths(vMaxDate,-2))

..then it will work. Just try and let me know

Regards,

Dawar

Re: Set Expression Help

ankitaag — Tue, 28 Apr 2015 11:20:32 GMT

Refer the attached application

You just need to create 2 variables for MinDate and Max Date

Re: Set Expression Help

er_mohit — Tue, 28 Apr 2015 11:20:53 GMT

Try this but make sure your Date have same format

sum({<Date = {'>=$(=MonthStart(Max(Date), -3))<=$(=MonthEnd(Max(Date)-1))'},Year=,Month=>}Sale)

Re: Set Expression Help

tobias_klett — Tue, 28 Apr 2015 11:21:47 GMT

Hi Abhay,

note that your Month-Field is dual with e.g. a text "Feb" and a number "2".
When using it in Set Analysis you can write {Jan,Feb} or {'<3'}. Both with the same result.

Therefore you need to convert the result of your Variables into the text again like this:

=month(date#(max(Month),'MM')) or =month(makedate(2000,max(Month),1))

or alternatively you can write your set Statement like this:

=sum({<Month={'<=$(vMaxDate)>=$(vMaxDate)}>}sales)

Hope this helps

Tobias