topic Re: Sum if, with nested sum expression, any ideas? in QlikView

Sum if, with nested sum expression, any ideas?

Wed, 22 Jul 2015 09:02:27 GMT

Hi all,

I'm having some issues with a sum(if expression which contains some nested statements, I;ve tried many variations but can't get it to work.

Any idaes much appreciated,

Expression as follows:

sum({<Trade_90_WorkDayFlag = {1} >}
if(aggr(sum(AnnualisedSales), [Client]) > vAnnSalesSlider ,
   (((sum(TOTAL <[Client Classification]> {$<Trade_90_WorkDayFlag= {1} >} $(eSales)))-
    (sum(TOTAL <[Client Classification]> {$<[Client] = ,Trade_Prev_90_WorkDayFlag= {1} >} $(eSales))))
     /(sum(TOTAL <[Client Classification]> {$<[Client] = ,Trade_Prev_90_WorkDayFlag= {1} >} $(eSales))))))

Even if we simplify this to :

sum({<[Client Parent Name] =, Trade_90_WorkDayFlag = {1} >}

if(aggr(sum(PostJVAnnualisedRevs), [Client Parent Name]) > vAnnREVsSlider ,

(((sum(TOTAL <[Client Classification]> {$<Trade_90_WorkDayFlag= {1} >} $(ePostJVCapEdge)))))))

I cannot get this to work

Basically translated it means:

If annualised sales per client is greater than the variable vAnnSalesSlider, complete the next part of the expression which gives me an average.

However, the sum if, with a nested sum does not work. Any ideas to get around this?

This is for a chart which does not contain client within it. It has one dimension - Client classification.

Thanks

Brendan

Re: Sum if, with nested sum expression, any ideas?

lironbaram — Wed, 22 Jul 2015 09:09:24 GMT

there is a problem with your logic

the if statement return an array by the aggr function and the other side return one value

you need to close the aggr function after the whole expression

what is the dimension of the calculation

Re: Sum if, with nested sum expression, any ideas?

Wed, 22 Jul 2015 09:32:12 GMT

It's client classification

Re: Sum if, with nested sum expression, any ideas?

lironbaram — Wed, 22 Jul 2015 10:46:56 GMT

what exactly do you intend to calculate

the client sales vs the total sales ?

Re: Sum if, with nested sum expression, any ideas?

Wed, 22 Jul 2015 11:02:28 GMT

think i may have sorted it, it is getting the client classification averages when the client sales meet the variable condition of vAnnSalesSlider over 90 days vs prev 90 days;

On a bar chart with dimension as client classification this works:

(sum(aggr(if(aggr(sum(AnnualisedSales), [Client]) > vAnnSalesSlider,
(sum({$<[Client] = ,Trade_90_WorkDayFlag= {1} >}$(eSales))/%Unit))
,[Client]))

-

sum(aggr(if(aggr(sum(AnnualisedSales), [Client Parent ID]) > vAnnSalesSlider,
(sum({$<[Client] = ,Trade_Prev_90_WorkDayFlag= {1} >}$(eSales))/%Unit))
,[Client])))

/

sum(aggr(if(aggr(sum(AnnualisedSales), [Client Parent ID]) > vAnnSalesSlider,
(sum({$<[Client] = ,Trade_Prev_90_WorkDayFlag= {1} >}$(eSales))/%Unit))
,[Client]))

Re: Sum if, with nested sum expression, any ideas?

lironbaram — Wed, 22 Jul 2015 11:15:34 GMT

yes ,

thats what i meant , now the aggr function is outside the if

and i am quite sure you can delete that you can delete the inner aggr function