11 Replies Latest reply: Jun 5, 2013 12:10 PM by Steve Zagzebski

# Set analysis - counting distinct

I am looking for set analysis help on the data below. Basically I need to count distinct groups that had less revenue in the current month than in the previous month. (in the example below group 3 and 5 had less revenue in the current month than the previous).

 DATA: DIVISION GROUP YEARMONTH REVENUE A 1 201301 90 A 1 201302 100 A 2 201301 110 A 2 201302 120 A 3 201301 150 A 3 201302 100 B 4 201301 100 B 4 201302 125 B 5 201301 140 B 5 201302 100 RESULT: Division YEARMONTH % of Groups with Less Revenue then Prior Month A 201302 33% B 201302 50%
• ###### Re: Set analysis - counting distinct

hi,

create two dynamic variables for Current and Previous Year Month

In the example:

vCurrentYearMonth= 201302

vCurrentYearMonth= 201301

and then try this set analysys=

=Count(DISTINCT

if( only({<YEARMONTH={\$(vCurrentYearMonth)}>}REVENUE ) < only({<YEARMONTH={\$(vPrevYearMonth)}>}REVENUE ),

GROUP))

Hope this helps.

MC

• ###### Re: Set analysis - counting distinct

See the attached file.

Basically a chart with Division as your dimension and the follwoing expression.  I've added a master calendar to make this easier and changed your dates so they actually are considered "This Month" and "Last Month"

=count(distinct AGGR(

If(1 - (sum({\$<DateRange={'Last Month'}>}REVENUE) / sum({\$<DateRange={'This Month'}>}REVENUE))

<1,DIVISION)

,DIVISION)

)

/

count(DISTINCT GROUP)

• ###### Re: Set analysis - counting distinct

Unfortunately I need this by month for the past 3 years so the concept of this month versus last month doesn't work in that scenario. This is probably a better depiction of needing to do this for multiple months...

 DATA: DIVISION GROUP YEARMONTH REVENUE A 1 201301 90 A 1 201302 100 A 1 201303 100 A 2 201301 110 A 2 201302 120 A 2 201303 90 A 3 201301 150 A 3 201302 100 A 3 201303 80 B 4 201301 100 B 4 201302 125 B 4 201303 100 B 5 201301 140 B 5 201302 100 B 5 201303 80 RESULT: Division YEARMONTH % of Groups with Less Revenue then Prior Month A 201302 33% B 201302 50% A 201303 66% B 201303 100%
• ###### Re: Set analysis - counting distinct

If YEARMONTH is required as a dimenion I don't see how this can work with set analysis.  Maybe someone else will help you out on this.

Another approach is to do it in the script.  You can load your data by month and compare the current row to the previous (month) using the previous() funciton.

• ###### Re: Set analysis - counting distinct

Thanks Mark - I actually was doing this in the script before using previous. Where I ran intop problems is there can be multiple revenue lines for a group so it looks at only the previous record line but there maybe be a couple of others also. Couldn't figure out a way around that.

• ###### Re: Set analysis - counting distinct

See attached example.

And set analysis creates one set for the entire chart, not per row. That's why using a field both as a chart dimension and in the set modifier often doesn't do what you would like.

• ###### Re: Set analysis - counting distinct

Thanks!  I am going to to try to incorporate this into our model.

• ###### Re: Set analysis - counting distinct

Gysbert has a good article on this:http://community.qlik.com/docs/DOC-4252

I should have read this first maybe

• ###### Re: Set analysis - counting distinct

The Current And Previous Month indicators are working great. I will use that alot.

However the aggregation isn't.

I broke your expression into two fields. The distinct group count (denominiator) is simple and works fine. However counting the groups only if the current month is less than the previous month just always brings back 0's ("Group (if Curr < Prev)"  column)

The expression is:

count(if(aggr(\$(vBenFactorCur) - \$(vBenFactorPrev),[Group ID],[Incurred Year Month (Analytical)]<0), [Group ID]))

Do you see anything wrong?

• ###### Re: Set analysis - counting distinct

Oops - my image didn't show up in my previous reply....

 Incurred Year Month (Analytical) Groups ( if Curr < Prev) Distinct Groups 0 1402 201211 0 1362 201212 0 1353 201301 0 1354
• ###### Re: Set analysis - counting distinct

One question for you - I struggle alot with aggregation (and advanced set analysis) in objects. Is it safe to say that aggregation and Set analysis should not include the dimensions in your object?