Set analysis - counting distinct

[zagzebski]

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%

[#2]

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

[RedSky001]

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)

[zagzebski]

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%

[RedSky001]

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.

[zagzebski]

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.

[zagzebski]

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?

[Gysbert_Wassenaar]

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.

[zagzebski]

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

[RedSky001]

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

I should have read this first maybe