Set analysis Grouping dimension

[mario-sarkis]

Dear All,

I Have a List of Account that has a previous Bucket B0,B1,B2,B3,.... And current Bucket will Change or still the same

I need to calculate in a Pivot table a ratio of for example the number of account that have previous bucket B0 / The number of account that become B0_B1 (or Become B1)

Please find the following example Thanks:

Database
PreviousBucket	Current Bucket	Dimension Bucket	Flag	Account
B0	B1	B0-B1	1	1
B0	B1	B0-B1	1	2
B0	B0	B0-B0	0	3
B0	B0	B0-B0	0	4
B1	B1	B1-B1	0	5
B1	B0	B1-B0	0	6
B1	B2	B1-B2	1	7
B1	B2	B1-B2	1	8
B1	B2	B1-B2	1	9
B2	B1	B2-B1	0	10
B2	B1	B2-B1	0	11
B2	B3	B2-B3	1	12
B2	B3	B2-B3	1	13
B2	B3	B2-B3	1	14
OutPut Ration of distinct accounts by Dimenssion Buckets
Dimension Bucket	Ratio	Values
B0-B1	(Number of Account of B0_B1)/(Number of Account that has as Previous Bucket 0)	0.50
B1-B2	(Number of Account of B1_B2)/(Number of Account that has as Previous Bucket 1)	0.6
B2-B3	(Number of Account of B2_B3)/(Number of Account that has as Previous Bucket 2)	0.6

[swuehl]

Try two dimensions, PreviousBucket and CurrentBucket.

As expressions:

=Count(DISTINCT Account)

=Count(DISTINCT Account) / Count(TOTAL<PreviousBucket> DISTINCT Account)

[mario-sarkis]

Hey Stefan

Thank you for you reply

But this Expression will count all Accounts of Previous Bucket??

I need to the Total Accounts of B0 incase The Dimension is B0_B1

and to The Total Accounts of B1 incase The Dimension is B1-B2 ,.....?

Thank you

[pradosh_thakur]

DIMENSION =      =IF(Flag=1,[Dimension Bucket],NULL())

MEASURE =      aggr(count([Dimension Bucket]),[Dimension Bucket]) / aggr(count( [Dimension Bucket]),PreviousBucket)

Attached is the QVW

regards

Pradosh

[swuehl]

Have you tried using the two dimensions and two expressions?