CODEWITHSUNDEEP
qlikview
B-Abbasi
B-Abbasi

Reputation: 823

Set Analysis in QlikView Current Vs Last Month

I am trying to show Current Month Stats in a column and Last month stats in another column. While using Set Analysis I am facing issue that I can not get numbers for Last month

This is the statement I am using but it does not work: 

/*Statement For Last Month*/
Sum( {$<CalMonthYear={"=$(=Max(CalMonthYear)-1)"}>} [DSP-Subscriber_Count])
/*Output here is the Sum of All Months */

Ideally it should give me the same output as following statement does

/***Last Month Hard Coded Script****/
Sum( {$<CalMonthYear={'Feb 2015'}>} [DSP-Subscriber_Count])

I have tried several variations but none seem to work. Any ideas as to what I might be doing wrong.

Thanks

Upvotes: 1

Views: 8881

Answers (3)

jabrane BELGHITH
jabrane BELGHITH

Reputation: 1

I found this answer useful for my problem, but what I need really is to have: Sum({$<[CalMonth]< ={$(vMaxSelectedDate_Num)}>}[Entity_To_Be_Added])

Upvotes: 0

B-Abbasi
B-Abbasi

Reputation: 823

Finally I was able to solve the issue by first storing selected month number in a variable and using that variable in Set Analysis Expression. Although I am still not sure why it wouldn't work without a variable. Anyway here is my solution for the problem

Declare Variable in Variable Overview Dialogue

vMaxSelectedDate_Num

Definition

= num(Month(Max(CalDate)))

Now Using CalMonth in Expression (CalMonth has Integer Value of Month)

Sum({$<[CalMonth]={$(vMaxSelectedDate_Num)}>}[Entity_To_Be_Added])

Similary I stored Last Month in a separate variable using below formula

= num(Month(AddMonths( Max(CalDate),-1)))

Upvotes: 1

fchukoskie
fchukoskie

Reputation: 346

You can't rely upon doing a -1 for a Month/Year combination, even if you're storing it as an integer (subtract 1 from 201501, for example...you don't get Dec 2014).

If you have day level dates in your model, do the arithmetic upon that. Perhaps something like:

{$<CalMonthYear={"=$(=Date(AddMonths(CurrentDate, -1), 'MMM YYYY'))"}>}

Upvotes: 3

Related Questions