Set analysis from the same field

[#1]

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

[Oleg_Troyansky]

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!

[sunny_talwar]

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,

S

[Frank_Hartmann]

can u provide sample?

[#5]

Yes, exactly.

[#6]

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

[Anonymous · Accepted solution]

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.

[Oleg_Troyansky]

so, did it work for you?

[#9]

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

[sunny_talwar]

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,

S