Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Contributor III

Sales for Month of previous year (Set Analysis)

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

Sales for Month of previous year (Set Analysis)

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
Honored Contributor

Sales for Month of previous year (Set Analysis)

Hi rjurd,

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

Does it return the same format?

Regards,

Sokkorn

Not applicable

Sales for Month of previous year (Set Analysis)

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
Contributor III

Re: Sales for Month of previous year (Set Analysis)

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

chauhans85
Esteemed Contributor

Sales for Month of previous year (Set Analysis)

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

Not applicable

Sales for Month of previous year (Set Analysis)

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

chauhans85
Esteemed Contributor

Sales for Month of previous year (Set Analysis)

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

rohit214
Contributor III

Re: Sales for Month of previous year (Set Analysis)

hey bro

put -12 in place of -1

may it helps you

thanks

rohit

Community Browser