Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
whill
Contributor
Contributor

rank total with aggr is still calculating on selections

i was able to make good progress with y'alls input to refine this calc, but i am still beating my head against the wall with this calculation involving rank(total aggr( ; it keeps referencing the visible results on straight table. . . any help would be greatly appreciated.  as a note, the calcs in the middle work as expected.  probably staring at this too long.

//rank(total aggr(
if(
if(
aggr(nodistinct rank(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER],key)
<=
sum(aggr(nodistinct aggr(nodistinct sum($(metric)),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])/aggr(nodistinct sum({1}$(metric)),Market,[MEGA CATEGORY])*$(SKUs),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])),1,'')
<>1,
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric))
*
aggr(rangesum(above(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE))
/
aggr(nodistinct avg({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr((sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric))
*
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(rangesum(above(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key))
/
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct rank(aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))
/
(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct max({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE)))),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE)
*100,'')
// ,Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))

1 Reply
whill
Contributor
Contributor
Author

i think that i have come to the conclusion that it is these portions that are causing my calculation issue when selections are made.

aggr(rangesum(above(...

if you know another way to calculate this please let me know.  thank you for y'alls input.

best