Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

5 Replies
lironbaram
Partner - Master III
Partner - Master III

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

Not applicable
Author

It's client classification

lironbaram
Partner - Master III
Partner - Master III

what exactly do you intend to calculate

the client sales vs the total sales ?

Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

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