Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Sum if, with nested sum expression, any ideas?

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

Tags (1)
5 Replies
Highlighted
Partner
Partner

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

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

Highlighted
Not applicable

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

It's client classification

Highlighted
Partner
Partner

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

what exactly do you intend to calculate

the client sales vs the total sales ?

Highlighted
Not applicable

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

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]))

Highlighted
Partner
Partner

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

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