Problems with my sum(AGGR functions

[leenlart]

Hello,

I have a table in my app that calculates the Constant Clients (ordered last year and this year), the Gained clients (No order last year) and the Lost Clients (No order this year).  I am trying to create a summary table of the number of clients and the volume for each client type.

Then I need a summary table for each client type that breaks it down into : Big Clients (>= 200 kg ordered), Medium clients (>=50 and <200) and Small clients (<50).  In the second summary table, I need to show Volume and count for Big, Medium and Small for Constants, Gained and Lost clients.

I haven't figured out a way to create a table to do this summary table, so I've been working on text boxes for each value.

I seem to have some sort of problem with the calculation of the Big Volume for each client type.  This comes out bigger than what I find when I use the table with all the clients and a column per expression.

Attached is a sample of my qdw.

Does anyone have any ideas that could help with this?

[jwjackso]

Have you tried creating calculated dimensions?

Category =

=Aggr(if ( (Sum({<TYPE_CALENDRIER={'YTD CAL'}>}fp_poids) > 0 and Sum({<TYPE_CALENDRIER={'YTD CAL N-1'}>}fp_poids) = 0),'Conquetes',

      (if ( (Sum({<TYPE_CALENDRIER={'YTD CAL'}>}fp_poids) > 0 and Sum({<TYPE_CALENDRIER={'YTD CAL N-1'}>}fp_poids) > 0),  'Constants',

       'Perdus'))),fp_poids)

Size =

=Aggr(if(sum({<TYPE_CALENDRIER={'YTD CAL','YTD CAL N-1'}>} fp_poids) >= 200,'Large',

If (sum({<TYPE_CALENDRIER={'YTD CAL','YTD CAL N-1'}>} fp_poids) >= 50 and sum({<TYPE_CALENDRIER={'YTD CAL','YTD CAL N-1'}>} fp_poids) < 200,'Medium','Small')),fp_poids)

[leenlart]

Ah ha!! 

I had tried but not gotten the syntaxe correct I guess.  But your solution really helps! 

Thanks!! 

[leenlart]

Ok, so now to take this one step further :

Instead of saying Big clients >=200, Small clients are <50 kg.  How can I make it so that Big clients are the top 20% of the total volume, the small clients are the bottom 50% and the medium clients are everyone else in the middle ? 

Can I make an aggr statement with this ? 

Should I try with dimension limits (but then do I need to create three identical dimensions where I set one limit as top 20%, one limit as bottom 50% and then the rest ?  )

Thanks!