topic Re: Set Analysis logic in QlikView

Set Analysis logic

Sun, 26 Feb 2012 20:30:49 GMT

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.

Set Analysis logic

Anonymous — Sun, 26 Feb 2012 21:11:58 GMT

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

Anonymous — Mon, 27 Feb 2012 00:28:54 GMT

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.

Re: Set Analysis logic

Mon, 27 Feb 2012 07:03:53 GMT

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.

Re: Set Analysis logic

CELAMBARASAN — Mon, 27 Feb 2012 07:34:27 GMT

Hi,

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

Hope it solves your problem

Celambarasan

Re: Set Analysis logic

Mon, 27 Feb 2012 15:09:10 GMT

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.