Calculate number of customers in amount intervals

[#1]

Hello,

i would like to create a table looking like this;

Interval	Q3 2009	Q3 2008	Q3 2007
>300 000	37	34	28
150 000 - 299 999	42	40	28
75 000 - 149 999	71	57	54
30 000 - 74 999	178	162	154
<29 999	1 375	1 394	1 422
Total	1 703	1 687	1 686

where the sum per interval is a total of distinct customernames per chosen period. I want this to be dynamic, so when a user makes a choice of one Q ("Q3 2009" in this case), the two preceding Q ("Q3 2008", "Q3 2007") are automatically calculated. I presume this is solved by aggr per customer and Q, but cannot sort out how to make the table look like above.

any suggestions?

thanks in advance,

Mikael

[martinpohl]

Hello Mikael,

for the dimension you need a dynamic dimension like

=if(aggr(Count ({<quartal={'Q3 2009'},>} distinct Customername) >300000,'> 300000',
if(aggr(Count ({<quartal={'Q3 2009'},>} distinct Customername) <300000,'150.000 - 299.999',
if(aggr(Count ({<quartal={'Q3 2009'},>} distinct Customername) <150000,'75.000 - 149.999',

if(aggr(Count ({<quartal={'Q3 2009'},>} distinct Customername) <75000,'30.000 - 74.999',

'<29.999')

[#3]

Thanks for a quick reply, i think however i missed out on some information, i apologise!

i need to sum up AMOUNT per Q for the calculation, i.e. i need to count number of customers, per Quarter, that had sum of AMOUNT in the stated intervals. What kind of adjustment to i need to make to the above formula?

and also, i want the Quarters to be chosen by user, meaning the first quarter should be chosen , and the two preceding to be based on first choice.

Regards,

Mikael