Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Dollar sign expansion calculation for every row in pivot

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.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

5 Replies
Highlighted

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

Highlighted
Specialist II
Specialist II

Something like this 

 

 sum(aggr( only(alleps) * 

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

McSoiScg,PPIR_ProvId))

)

 

 

 

 

 

 

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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

View solution in original post