topic Re: Selected Year Month vs last year in QlikView

Selected Year Month vs last year

Anonymous — Mon, 19 Feb 2018 14:09:57 GMT

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?

Re: Selected Year Month vs last year

vikraant — Mon, 19 Feb 2018 19:02:01 GMT

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

Re: Selected Year Month vs last year

Anonymous — Tue, 20 Feb 2018 12:28:26 GMT

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!!!!

Re: Selected Year Month vs last year

vikraant — Wed, 21 Feb 2018 07:36:35 GMT

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

Re: Selected Year Month vs last year

Anonymous — Wed, 21 Feb 2018 09:06:10 GMT

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