Dollar sign expansion calculation for every row in pivot

[VivenReddy]

I have been struggling to get the correct output in a pivot table expression.

sum(aggr(only({<mbname={"$(=FirstSortedValue(mbname,-aggr(Sum(Pick(targetid, lowexcess, acoexcess, midexcess, highexcess) * bundlecharges),McSoiScg,mbname)))"}>}alleps),McSoiScg,PPIR_ProvId))

This is as close i can get it to kinda work based on certain selections. I noticed that dollar sign expansion gets calculated once and applies that result to all rows. I need it to recalculate for every row. After some investigating I found that is how qlikview charts are meant to work. I am unable to find to suitable workaround. Is there anyone who can point me in the right direction?

Sorry I'm unable to post my qvw for various reasons.

[sunny_talwar]

You can use Aggr() with if statement if you want your condition to be evaluated on certain dimensions....

[qliksus]

Something like this 

 sum(aggr( only(alleps) * 

(mbname=FirstSortedValue(mbname,-aggr(Sum(Pick(targetid, lowexcess, acoexcess, midexcess, highexcess) * bundlecharges),McSoiScg,mbname) ))

McSoiScg,PPIR_ProvId))

)

[VivenReddy]

Thank you for your suggestion Qliksus. Can you explain what your expression is meant to do please. It is currently not returning any values. If i Can understand what its meant to do then I can try to adjust the syntax.

[VivenReddy]

Thanks Sunny.

Can you please clarify how to use Aggr() with if statement to meet my requirement.

I've tried the expression below but it does not work.

if(mbname=FirstSortedValue(mbname,-aggr(Sum(Pick(targetid,lowexcess,acoexcess,midexcess,highexcess) * bundlecharges),McSoiScg)),
sum(aggr(only(alleps),mcname,soi,scgname,PPIR_ProvId,mbname, RowDim)))

[VivenReddy · Accepted solution]

Thank you for suggestions guys.

I was being stupid. The obvious solution for me was to return the correct value from the FirstSortedValue function. Then DSE is not required.

sum(aggr(FirstSortedValue(alleps,-aggr(Sum(total<McSoiScg, mbname>Pick(targetid, lowexcess, acoexcess, midexcess,highexcess) * bundlecharges),McSoiScg, mbname)),McSoiScg,PPIR_ProvId))