topic Re: Problems with my sum(AGGR functions in QlikView

Problems with my sum(AGGR functions

leenlart — Fri, 09 Feb 2018 16:59:08 GMT

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?

Re: Problems with my sum(AGGR functions

jwjackso — Fri, 09 Feb 2018 22:12:02 GMT

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)

Re: Problems with my sum(AGGR functions

leenlart — Mon, 12 Feb 2018 09:09:58 GMT

Ah ha!!

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

Thanks!!

Re: Problems with my sum(AGGR functions

leenlart — Mon, 12 Feb 2018 10:37:53 GMT

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!