count if based on an expression

[coloful_architect]

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

[coloful_architect]

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

[Kushal_Chawda · Accepted solution]

try below

count(Aggr(if(Rank(sum([Sales in Unit)]))<3,[ (MAT).Global Manufacturer]), [Product], [ company]))

[coloful_architect]

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 

[Kushal_Chawda]

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

[coloful_architect]

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

[Kushal_Chawda]

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