ignoring filter selections within an aggr rank equation

[whill]

i am having trouble getting this equation to ignore selections beyond Market and Mega Category.  Any help would be appreciated!

round(rank(total if(
if(
aggr(nodistinct rank(aggr(nodistinct sum($(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($(metric))
*
aggr(rangesum(above(sum($(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(aggr(nodistinct sum($(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max($(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE))
/
aggr(nodistinct avg(aggr((sum($(metric))
*
aggr(rangesum(above(sum($(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(aggr(nodistinct sum($(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max($(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,'')))

[yilmazmurat · Accepted solution]

You canthe code below

Count({<[Ignored_field] >} Field )

[whill]

thank you for the quick response.  i tried something similar, sum({<ignored_filed=>}Field), with no luck.   i implemented your suggestion, but the results are still being filtered and recalculating.  please let me  know if you see that i misapplied your suggestion below.

round(rank(total if(
if(
aggr(nodistinct rank(aggr(nodistinct sum({<CATEGORY>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER],key)
<=
sum(aggr(nodistinct aggr(nodistinct sum({<CATEGORY>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])/aggr(nodistinct sum({1<CATEGORY>}$(metric)),Market,[MEGA CATEGORY])*$(SKUs),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])),1,'')
<>1,

////////
sum({<CATEGORY>}$(metric))
*
aggr(rangesum(above(sum({<CATEGORY>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(aggr(nodistinct sum({<CATEGORY>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max({<CATEGORY>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE))
/
aggr(nodistinct avg({<CATEGORY>}aggr((sum({<CATEGORY>}$(metric))
*
aggr(rangesum(above(sum({<CATEGORY>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(aggr(nodistinct sum({<CATEGORY>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max({<CATEGORY>}$(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,'')))

[whill]

this looks to work on my test expression.  thank you for the help, and getting me in the right direction!  i just needed to add-in the sum() within the rank.

best!

aggr(nodistinct rank(sum({<CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key>}aggr(nodistinct sum({<CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER],key)