Set Analysis logic

[#1]

Hi, dear colleagues.

Could you help me, please.

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.

[Anonymous]

Oleg,:

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

[Anonymous]

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.

ADDMONTHS([Month],1)  AS PrevMonth,

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.

[#4]

Hi, Michael.

I thought about it. But my task is more difficult.

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.

[CELAMBARASAN · Accepted solution]

Hi,

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

Hope it solves your problem

Celambarasan

[#6]

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.