Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
gkourogiorgas
New Contributor II

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
Contributor

Re: Selected Year Month vs last year

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

gkourogiorgas
New Contributor II

Re: Selected Year Month vs last year

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
Contributor

Re: Selected Year Month vs last year

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

gkourogiorgas
New Contributor II

Re: Selected Year Month vs last year

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