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%

[zagzebski]

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?

[zagzebski]

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