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: 
Not applicable

Sales for Month of previous year (Set Analysis)

I'm sure i'm just missing something simple for this one....

all my dimensions are real dimensions and not calculated.  There are no selections made in the doc.

My pivot dimensions are:

  • product
  • year "SaleYear"
  • month "SaleMonth"

the expressions i want are:

  • sales for the month from the dimension --> easy "Sum(Sales)"
  • sales for the month of the previous year for that dimension <---- this is just giving me $0 "Sum({1<sale_date={"=InMonth(sale_date,SaleMonth,-12)"}>} Sales)"

any ideas?

9 Replies
rohit214
Creator III
Creator III

hi

try this

Sum({1<sale_date={"$(=InMonth(sale_date,SaleMonth,0)"}>} Sales)

may it helps u .plz let me kn

thanks

rohit

Not applicable
Author

unfortunately not... that just gives me blanks...  not sure why you changed it from -12 to 0?  i need the sales from the same month a year ago

Sokkorn
Master
Master

Hi rjurd,

Check sale_date and InMonth(sale_date,SaleMonth,-12)

Does it return the same format?

Regards,

Sokkorn

Not applicable
Author

inmonth returns a boolean but my understanding is that inside a set it will just return all the sale_date that match so in theory should return the sales from the previous year's month.

the dollar sign expansion suggested by rohit214 i suspect doesn't work because of the format you've mentioned

rohit214
Creator III
Creator III

hi

i m sorry for that

use this

Sum({1<sale_date={"$(=InMonth(sale_date,((SaleMonth)-1),0)"}>} Sales)

using salemonth-1 means  to get the data previous yaer

may it helps you

thanks

'rohit

SunilChauhan
Champion
Champion

i believ you need to calculate monthYear in script  from Date using below

Monthname(date) as MonthYear

the n use expression

Sum({1<MonthYear={"$(=addmonths(MonthYear,-1))"}>} Sales)

hope this help

Sunil Chauhan
Not applicable
Author

Hi Sunil,

that just gave me $0... I changed yours to -12 from -1 as i want the previous year.

rohit214 - your example looks like it would only return the previous month, not the same month from the previous year...

SunilChauhan
Champion
Champion

share the sample fil so that i can have a look.

Sunil Chauhan
rohit214
Creator III
Creator III

hey bro

put -12 in place of -1

may it helps you

thanks

rohit