Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
Champion III
Champion III

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.

View solution in original post

10 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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!

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

Highlighted

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

Highlighted
Master II
Master II

can u provide sample?

Highlighted
Not applicable

Yes, exactly.

Highlighted
Not applicable

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

Highlighted
Champion III
Champion III

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.

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

so, did it work for you?

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

Highlighted
Not applicable

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

Highlighted

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