Count function

[shamitshah]

Hi,

I am trying to count the number of items that are doing better compared to the previous year. I have two variables set up as follows which shows the total sales for the respective years:

v1)=num(sum(AGGR(SUM({<FiscalYear={2016}>}SLL_PriceExc),[Item Number])),'#.')

v2)= num(sum(AGGR(SUM({<FiscalYear={2015}>}SLL_PriceExc),[Item Number])),'#.')

I tried to use the following formula but it did not give any results:

=num(Count(DISTINCT( if (v1>v2,[Item Number]))))

Any ideas?

Thanks

Shamit

[Chanty4u]

=sum(agg(Count(

if (v1>v2,

DISTINCT[Item Number]))))

[sunny_talwar]

Can you change your variable to this:

v1 = Aggr(Sum({<FiscalYear={2016}>}SLL_PriceExc),[Item Number])

v2 = Aggr(Sum({<FiscalYear={2015}>}SLL_PriceExc),[Item Number])

and then try this:

Count(DISTINCT(If(v1 > v2, [Item Number]))

Have not tested it out, so not 100% sure if this is going to work or not

[Chanty4u]

=num(Count(DISTINCT( if (v1>v2,[Item Number]))),'#.')

[shamitshah]

Hi Sunny & Chanty

I tried what you mentioned above, but is does not seem to work.

Thanks

Shamit

[sathishkumar_go]

post your application with sample data

-Sathish

[sunny_talwar]

Can you share a sample?

[shamitshah]

Hi,

Please see attached file.

The number of items with a positive variance in the text box should be 1 instead of 3.

Thanks

Shamit

[Gysbert_Wassenaar · Accepted solution]

Try =Count({<Item={"=sum({<Year={2016}>}Sales)>sum({<Year={2015}>}Sales)"}>}DISTINCT Item)

[sunny_talwar]

You can apply Gysbert's logic with variables also:

=Count({<Item={"=$(v1)>$(v2)"}>}DISTINCT Item)

Where v1 is defined as 'Sum({<Year={2016}>}Sales)' and v2 is defined as 'Sum({<Year={2015}>}Sales)'