topic Re: Count function in QlikView

Count function

shamitshah — Fri, 19 Feb 2016 00:56:14 GMT

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

Re: Count function

Chanty4u — Fri, 19 Feb 2016 10:09:58 GMT

=sum(agg(Count(

if (v1>v2,

DISTINCT[Item Number]))))

Re: Count function

sunny_talwar — Fri, 19 Feb 2016 10:10:19 GMT

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

Re: Count function

Chanty4u — Fri, 19 Feb 2016 10:12:42 GMT

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

Re: Count function

shamitshah — Fri, 19 Feb 2016 14:10:48 GMT

Hi Sunny & Chanty

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

Thanks

Shamit

Re: Count function

sathishkumar_go — Fri, 19 Feb 2016 14:18:10 GMT

post your application with sample data

-Sathish

Re: Count function

sunny_talwar — Fri, 19 Feb 2016 14:29:54 GMT

Can you share a sample?

Re: Count function

shamitshah — Sat, 20 Feb 2016 01:57:48 GMT

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

Re: Count function

Gysbert_Wassenaar — Sat, 20 Feb 2016 10:42:23 GMT

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

Re: Count function

sunny_talwar — Sat, 20 Feb 2016 12:57:23 GMT

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

Re: Count function

shamitshah — Sat, 20 Feb 2016 14:43:18 GMT

Thanks you Gysbert and Sunny. Both correct answers.

Awesome!

Shamit