10 Replies Latest reply: Feb 13, 2015 4:10 PM by Joe Crane

# Set analysis from the same field

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

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

Yes, exactly.

• ###### Re: Set analysis from the same field

so, did it work for you?

• ###### Re: Set analysis from the same field

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

• ###### Re: Set analysis from the same field

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

Best,

S

• ###### Re: Set analysis from the same field

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

• ###### Re: Set analysis from the same field

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

• ###### Re: Set analysis from the same field

can u provide sample?

• ###### Re: Set analysis from the same field

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

Thanks, the above function worked like a charm.

Joe