Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I guess this might be easy for you.
I am struggling with a count expression. Want to return a number to count the frequency of a company have top 1 and top 2 positions. so if this company at product x and y has ranked no.1 and no.2 , it returns me 2; if only ranked one no.1, it returns me 1. if no top 2 ranks, if returns me 0.
I am able to figure out the expression for the Unit sales Rank by company for each product
Aggr(Rank(sum([Sales in Unit)])), [Product], [ company])
But how do I insert this into count formula to filter out those total count of ranks less than 3?
Tried
count(Aggr(Rank(sum([Sales in Unit)])), [Product], [ company])<'3',[ Company])
but it does not work.
any idea?
Company | Product | Unit sales Rank by company for each product | Want to display total count of Top 1 and 2 |
A | X | 1 | 2 |
B | X | 2 | 1 |
C | X | 3 | 1 |
A | Y | 4 | 2 |
B | Y | 3 | 1 |
C | Y | 2 | 1 |
D | Y | 1 | 0 |
try below
count(Aggr(if(Rank(sum([Sales in Unit)]))<3,[ (MAT).Global Manufacturer]), [Product], [ company]))
and I built an variable for "Aggr(Rank(sum([Sales in Unit)])), [Product], [ company])" as vCY Rank
when I insert it into if count fiction like this
=if($(vCY Rank)<'3', count([ company]))
it returns me the total count of products for company..seems the variable does not work.
any idea
try below
count(Aggr(if(Rank(sum([Sales in Unit)]))<3,[ (MAT).Global Manufacturer]), [Product], [ company]))
Hi Kush,
this works perfect! Thanks a lot...Would you please elaborate a bit more why it works? as a newbie, I feel a bit hard to follow your if function inserted into the rank function...
or maybe shed some lights on why my variable does not work....is that the '3' set expression wrong?
much appreciated
variable should work but the expression in your variable is designed to calculate Ranks, hence if you count the number based on that expression you will lose the aggr scope in which you should count. Therefore count will always give you total count. So only change I did is to include the count in aggr scope so that based on rank it will correctly calculate the count
Thanks so much Kush.
one more question...based on this
count(Aggr(if(Rank(sum([Sales in Unit)]))<3,[ (MAT).Global Manufacturer]), [Product], [ company]))
I try to count all sales units bigger than 0 so I added an set expression {<[Sales in Units (MAT PY)]={'>0'}>}
the complete version is
Count(Aggr(if
(Rank
(sum({<[Sales in Units (MAT PY)]={'>0'}>}
[Sales in Units (MAT PY)] ))<3,[Global Manufacturer]), [Substance Grp.], [Global Manufacturer]))
However, it does Not return the expected result. Any sugggestions?
Thanks
@coloful_architect You need to use set analysis in outer aggregation as well
Count({<[Sales in Units (MAT PY)]={'>0'}>}Aggr(if
(Rank
(sum({<[Sales in Units (MAT PY)]={'>0'}>}
[Sales in Units (MAT PY)] ))<3,[Global Manufacturer]), [Substance Grp.], [Global Manufacturer]))