Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Selected Year Month vs last year

Hi

I am trying to get sales for the selected (through a filter pane) Year-Month and I want also to calculate the sales for the same month but previous year.

Here is what I have so far:

sum(

{1<Accumulator_Type={'NEW_ORDER'},Order_Channel={'DIRECT'}

,[Transaction_Date.autoCalendar.Year]={"=$(=vLY)"}

,[Transaction_Date.autoCalendar.Month]]={"=$(=vMonth)"}>}

Value-Tax)

where vLY = Year([Transaction_Date.autoCalendar.YearMonth])-1

and vMonth = =[Transaction_Date.autoCalendar.Month]

when I put these variables in text box they show the right values when I select a Year-Month

Any ideas?

4 Replies
vikraant
Creator
Creator

Hi,

You need to use max function in order to get a static values for the year and month in the variables.

Value for previous year can be obtained by using the max function :

vLY  = Year(Max([Transaction_Date.autoCalendar.YearMonth]))-1

Value for current month can be obtained, again by using the max function

vMonth  = Max([Transaction_Date.autoCalendar.Month])

Expression for previous year sales is as follows :

sum({<Accumulator_Type={'NEW_ORDER'},Order_Channel={'DIRECT'}

,[Transaction_Date.autoCalendar.Year]={"=$(vLY)"}

,[Transaction_Date.autoCalendar.Month]]={"=$(vMonth)"}>}

Value-Tax)

Hope this helps.

Best Regards,

Vikraant

Anonymous
Not applicable
Author

You are right although max is optional. What was missing was a Month() function to wrap the Transaction.autocalendar.Month

For some reason, although the value is correct in a text box, the set analysis does not accept it!!!!

vikraant
Creator
Creator

Are the variables $(vMonth) and $(vLY) showing correct values in text box, even without selections? 

Anonymous
Not applicable
Author

When $(vMonth) = [Transaction_Date.autoCalendar.YearMonth] then No

When $(vMonth) = month(max([Transaction_Date.autoCalendar.YearMonth])) then yes

When $(vMonth) = max([Transaction_Date.autoCalendar.YearMonth]) then i get the value 43132