Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
It's client classification
what exactly do you intend to calculate
the client sales vs the total sales ?
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]))
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