topic Re: Set analysis from the same field in QlikView

Set analysis from the same field

Fri, 13 Feb 2015 20:41:35 GMT

Hi,

I'm trying to set something on a table chart that uses an equation that pulls from a previous month/year id:

=(sum({$<SNAPSHOT_MONTH_ID = {SNAPSHOT_MONTH_ID}-1>}, MembershipFlag))/(sum(MembershipFlag))

I want to divide that 2nd snapshot_month_id by the first one. I think my set analysis is wrong though. I simply want to subtract 1 from the current month ID. Is there a way to do this?

Thanks,

Joe

Re: Set analysis from the same field

Oleg_Troyansky — Fri, 13 Feb 2015 20:48:32 GMT

Hmmm, it's unclear what do you mean by "previous" Month - compared to the highest Month available, or compared to the Month in the data... The following example filters the data for the previous month compared to the highest available month:

=(sum({$<SNAPSHOT_MONTH_ID = {$(=max(SNAPSHOT_MONTH_ID)-1)}>} MembershipFlag)) /(sum(MembershipFlag))

Now, this is assuming that all Months are numbered sequentially, so the difference between Dec 2014 and Jan 2015 is one - right?

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Re: Set analysis from the same field

sunny_talwar — Fri, 13 Feb 2015 20:50:14 GMT

What is the dimension of your table? If it is SNAPSHOT_MONTH_ID, then you would need to use RangeSum(Above(...)) function, if not then you can try this

=(sum({$<SNAPSHOT_MONTH_ID = {'$(=MonthName(AddMonths(Date(Max(SNAPSHOT_MONTH_ID)),-1))'}>} MembershipFlag))/(Sum({$<SNAPSHOT_MONTH_ID = {'$(=MonthName(Date(Max(SNAPSHOT_MONTH_ID)))'}>} MembershipFlag))

This is all assuming that your SNAPSHOT_MONTH_ID is created in the script using the function MonthName()

Best,

Re: Set analysis from the same field

Frank_Hartmann — Fri, 13 Feb 2015 20:50:35 GMT

can u provide sample?

Re: Set analysis from the same field

Fri, 13 Feb 2015 20:51:48 GMT

Yes, exactly.

Re: Set analysis from the same field

Fri, 13 Feb 2015 20:53:14 GMT

The month ids are literally date#s like 201401, 201402, 201403... you get the picture.

Re: Set analysis from the same field

Anonymous — Fri, 13 Feb 2015 20:53:29 GMT

There is a syntax error, it should be rather:
=(sum({$<SNAPSHOT_MONTH_ID = {"SNAPSHOT_MONTH_ID-1"}>}, MembershipFlag))/(sum(MembershipFlag))

But there is a bigger problem. If SNAPSHOT_MONTH_ID is a chart dimension, it's not going to work because set evaluates all the selected data, not per chart row. You probably need to use before() or above() function, depending on your table type.

Re: Set analysis from the same field

Oleg_Troyansky — Fri, 13 Feb 2015 20:53:40 GMT

so, did it work for you?

Re: Set analysis from the same field

Fri, 13 Feb 2015 20:58:00 GMT

It did not. It's coming up as 0 for that sum.

Re: Set analysis from the same field

sunny_talwar — Fri, 13 Feb 2015 20:59:55 GMT

What is your dimension, Like I said and as also pointed by msolomov, you will need above or below function if your dimension is SNAPSHOT_MONTH_ID

Best,

Re: Set analysis from the same field

Fri, 13 Feb 2015 21:10:41 GMT

Thanks, the above function worked like a charm.

Joe