topic Re: Set analysis - counting distinct in QlikView

Set analysis - counting distinct

zagzebski — Mon, 03 Jun 2013 15:02:00 GMT

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

Mon, 03 Jun 2013 15:55:19 GMT

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.

Re: Set analysis - counting distinct

RedSky001 — Mon, 03 Jun 2013 16:10:32 GMT

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

zagzebski — Mon, 03 Jun 2013 20:26:45 GMT

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

RedSky001 — Tue, 04 Jun 2013 16:34:19 GMT

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

zagzebski — Tue, 04 Jun 2013 16:38:26 GMT

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

zagzebski — Tue, 04 Jun 2013 16:50:47 GMT

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?

Re: Set analysis - counting distinct

Gysbert_Wassenaar — Tue, 04 Jun 2013 20:05:39 GMT

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

zagzebski — Tue, 04 Jun 2013 20:56:48 GMT

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

Re: Set analysis - counting distinct

RedSky001 — Tue, 04 Jun 2013 21:52:09 GMT

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

zagzebski — Wed, 05 Jun 2013 16:06:39 GMT

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

zagzebski — Wed, 05 Jun 2013 16:10:35 GMT

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