5 Replies Latest reply: Feb 27, 2012 10:09 AM by oleg61858

# Set Analysis logic

Hi, dear colleagues.
I have a table, which contains 2 fields:
Month          Values
Jan 2012     100
Dec 2011     200
Nov 2011     300
etc.
I want to get value of previous month.
My expression is:
sum
({<Month = {"\$(=MonthName(Month, -1 ))"}>}Vallue)
But it works only if 1 month is selected (like Only(Month)) and doesn't work without any selection.
Is it possible to calculate dimension for each value of dimension "Month"?
Thanks a lot.
• ###### Set Analysis logic

Oleg,:

If you're using in in a table/chart where Month is dimension, try function above(), for example:
above(sum(Value))

• ###### Re: Set Analysis logic

Hi, Michael.

I need to bulid a chart, which shows a difference between current value and last month value.

Dimension "Month" is limited (last 6 months).

To limit the dimension i'm using calculated dimension

=if(Month> MonthStart(Today(), -6), Month)

if i use above function in expression, value of "Sep 2011" is null, because it don,t see the value of "Aug 2011".

That's why I,m considering set analysis as a solution.

It will be correct if I show last 7 month and "Sep 2011" will have not null value. In this case I need to hide "Aug 2011"

Thanks.

• ###### Re: Set Analysis logic

Hi,

Check with this link http://community.qlik.com/message/195238 which is similar to yours.

Celambarasan

• ###### Re: Set Analysis logic

Thanks a lot.

It's working.

Correct expression is:

sum(Value) - alt(above(Sum(Value)), FirstMonth)

where FirstMonth is expression:

if(GetSelectedCount(Month) =0,

Sum({<Month={"\$(=MonthName(today(), -6))"}>} TOTAL Value),

Sum({<Month={"\$(=MonthName(Min(total Month), -1))"}>} TOTAL Value))

"if"-statement makes it working with a selection in a Month field.

• ###### Re: Set Analysis logic

Hi Oleg,

I was thinking it could be done a different way.

for example:   if you where to add another field in the script which sets a new field to the previous month.

then use this field in your SetAnalysis, ie: SUM( {< [Month] = P(PrevMonth) >} Values)

that this should work, but preliminary testing (see attachment) did not work.  I'm pretty sure this should work and that I need some refinement of the SetAnalysis.  I'll keep playing as time permits but someone else may be able to advance this idea in the meantime.

Regards,

John.