Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a requirement where I am using expressions for amount and sale as:
Amount:
Num(sum(aggr(avg(MRC11),[Account Number],[Description])) ,'$#,##0.00'),
Sale:
Num(Aggr(SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),
If I am applying above expressions with aggr I am getting null values.Please help me with this.
ExpectedAmount | ActualAmount | ExpectedSale | ActualSale |
---|---|---|---|
22 | 22 | 12 | 12 |
34 | 34 | 12 | 12 |
34 | 34 | 2 | 2 |
Total=56 | Total=- | Total=26 | Total=- |
May be use DISTINCT Keyword for this
Can you please explain where can I use it in my expression
Num(Aggr(SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),
Like this?? vanivadlamudi
<<Modifications>>
SALES
=num(if(rowno()=0,Sum(aggr(avg(Sale),AccountNumber,Region)),sum(aggr(avg(Sale),AccountNumber,Desc))),'€ #,##0.00')
AMOUNT
=num(if(rowno()=0,Sum(aggr(avg(Amount),AccountNumber,Region)),sum(aggr(avg(Amount),AccountNumber,Desc))),'€ #,##0.00')
try this
Num(Aggr(distinct SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),
It was also not working
Try simple Sum(DISTINCT Net)
And then check partial sum from presentation and then show me the image
HI,
Try below Expression,
Num(Sum(AGGR(DISTINCT Sum(Net),[Account Number],Region,Country,City)),'€#,##0.00')
Thank you Anil. It worked
but I used it like
Num(Sum(DISTINCT Aggr(SUM(Net),[Account Number],[Service Description]),'€#,##0.00')
I don`t think simple distinct SUM will work if you have same net value for another combination. ( Region, Country , City, Account Number ), do check once .
Region='SOUTH' county='India', city='Delhi', AccNo=1011 , Net =100
Region='SOUTH' county='Aus', city='Sidny', AccNo=222 , Net =100
in this case SUM( distinct Net ) =100 instead of 200 ( as per my understanding)
Ya Sasi
thats why I used AGGR function and I have mentioned it in my post