Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
whill
Contributor
Contributor

ignoring filter selections within an aggr rank equation

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

1 Solution

Accepted Solutions
yilmazmurat
Contributor III
Contributor III

You canthe code below

 

Count({<[Ignored_field] >} Field )

View solution in original post

3 Replies
yilmazmurat
Contributor III
Contributor III

You canthe code below

 

Count({<[Ignored_field] >} Field )

whill
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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)