Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

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.

10 Replies
MVP & Luminary
MVP & Luminary

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

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
Honored Contributor

Re: Set analysis from the same field

can u provide sample?

Not applicable

Re: Set analysis from the same field

Yes, exactly.

Not applicable

Re: Set analysis from the same field

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

mov
Esteemed Contributor III

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.

MVP & Luminary
MVP & Luminary

Re: Set analysis from the same field

so, did it work for you?

Not applicable

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 msolomov, you will need above or below function if your dimension is SNAPSHOT_MONTH_ID

Best,

S