topic Re: Last Month Units - Set Analysis in QlikView

Last Month Units - Set Analysis

Anonymous — Thu, 28 Jan 2016 10:50:21 GMT

Hi all,

I'm trying to show the number of units sold for last month using set analysis. My expression I have is below which I thought would work but doesn't.

Any help would be much appreciated.

sum({<UNIT.SOLD_MONTH = {"$(=month(addmonths(today),-1))"}>} UNIT.TOTALSOLD)

Thanks

Gareth

Re: Last Month Units - Set Analysis

sunny_talwar — Thu, 28 Jan 2016 10:53:48 GMT

I think you are missing open and close parenthesis for Today() function. Try this:

Sum({<UNIT.SOLD_MONTH = {"$(=Month(AddMonths(Today(),-1)))"}>} UNIT.TOTALSOLD)

Re: Last Month Units - Set Analysis

Anonymous — Thu, 28 Jan 2016 10:55:55 GMT

Well spotted Sunny T but it hasn't solved the problem unfortunately.

Thanks

Re: Last Month Units - Set Analysis

sunny_talwar — Thu, 28 Jan 2016 11:00:03 GMT

There was also missing parenthesis at the end. Have you tried copy pasting the expression I have posted above?

Re: Last Month Units - Set Analysis

Anonymous — Thu, 28 Jan 2016 11:01:55 GMT

Yes I've copied and pasted your expression it's still blank?

Re: Last Month Units - Set Analysis

sunny_talwar — Thu, 28 Jan 2016 11:06:55 GMT

Can you share the values within UNIT.SOLD_MONTH

Re: Last Month Units - Set Analysis

sasikanth — Thu, 28 Jan 2016 11:07:07 GMT

hi,

UNIT.SOLD_MONTH is Text or Number?

did you check it?

Re: Last Month Units - Set Analysis

Anonymous — Thu, 28 Jan 2016 11:09:16 GMT

Hi,

Unit.Sold_Month is in the format MMM-YY

Re: Last Month Units - Set Analysis

PradeepReddy — Thu, 28 Jan 2016 11:13:10 GMT

try like this...

Sum({<UNIT.SOLD_MONTH = {"$(=Date(AddMonths(Today(),-1),'MMM-YY'))"}>} UNIT.TOTALSOLD)

Re: Last Month Units - Set Analysis

MayilVahanan — Thu, 28 Jan 2016 11:13:13 GMT

Try like this

Sum({<UNIT.SOLD_MONTH = {"$(=Date(AddMonths(Today(),-1),'MMM-YY'))"}>} UNIT.TOTALSOLD)

Re: Last Month Units - Set Analysis

MK_QSL — Thu, 28 Jan 2016 11:13:17 GMT

Use below expression

=SUM({<

UNIT.SOLD_MONTH= {"$(=Month(AddMonths(Today(),-1)))"},

UNIT.SOLD_YEAR= {"$(=Year(AddMonths(Today(),-1)))"}

>} UNIT.TOTALSOLD)

Re: Last Month Units - Set Analysis

sunny_talwar — Thu, 28 Jan 2016 11:16:02 GMT

The above is assuming that UNIT.SOLD_MONTH is a field which is date field as well. I would check if that's true by checking if Num(UNIT.SOLD_MONTH) in a list box gives you number. If it does than Mayil and Pradeep's expression should work for you. Else fix the UNIT.SOLD_MONTH in the script

Re: Last Month Units - Set Analysis

sasikanth — Thu, 28 Jan 2016 11:16:12 GMT

hi,

Try to take one separate field for month and use the same in the expression

Sum({< Month_field = {"$(=Month(AddMonths(Today(),-1)))"}>} UNIT.TOTALSOLD)

exp is like :

Sum({<UNIT.SOLD_MONTH = {"$(=Date(AddMonths(Today(),-1),'MMM-YY'))"}>} UNIT.TOTALSOLD)

Re: Last Month Units - Set Analysis

Anonymous — Thu, 28 Jan 2016 11:21:37 GMT

Thank you so much for everyone's help and advice!

Thanks

Gareth