topic Re: Count companies (issue found) in QlikView

Count companies (issue found)

maxsheva — Fri, 28 Jul 2017 11:17:16 GMT

Hi,

The discussion was before Count companies

For now I found one issue there.

data:

LOAD * Inline [

Date, Company, Product, Stocks

01/01/2017, Company1, Product1, 1

02/01/2017, Company2, Product1, 1

03/01/2017, Company3, Product1, 1

03/01/2017, Company1, Product1, -1

01/01/2017, Company1, Product2, 1

02/01/2017, Company2, Product2, 1

03/01/2017, Company3, Product2, 1

03/01/2017, Company3, Product2, -1

];

Expression is

Count(distinct {<[Date]={'>=(=min([Date]))<=$(=max([Date]))'},Company= {"=Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks) > 0"}>} Company)

I need to find distinct quantity of Companies per each date where min date is first date in table and max date is current selected.

When filter date 03/01/2017 the count shows incorrect figures.

As far as I see it takes company from another product.

When in addition filtered one of product it shows correct result.

I tried Aggr by Product but no affect unfortunately.

You may find qvw as attachment.

Any help would be appreciated.

Re: Count companies (issue found)

sunny_talwar — Fri, 28 Jul 2017 11:28:51 GMT

I am not sure I follow your description here

I need to find distinct quantity of Companies per each date where min date is first date in table and max date is current selected.

Would you be able to elaborate a little more? Also, when 03/01/2017 is selected, what is the expected count you wish to see?

Re: Count companies (issue found)

maxsheva — Fri, 28 Jul 2017 11:42:12 GMT

As result should be distinct count of companies where stocks > 0

Stocks calculates as sum for period.

For example Product1 on 03/01/2017 has zero.

on 01/01/2017 it is 1

on 03/01/2017 it is -1

sum(stocks) on 03/01/2017 = 0

The correct result for Product1 (distinct count of companies) on 03/01/2017 is two companies.

It means only two companies has stocks on this date.

Expression above shows three companies for Product1 when select 03/01/2017

If you select Product1 in additional it will show two companies as result of expression.

I can't find what is missed there.

Re: Count companies (issue found)

Kushal_Chawda — Fri, 28 Jul 2017 12:04:41 GMT

this seems to be working

=Count( {<[Date]={'>=(=min([Date]))<=$(=max([Date]))'},Company= {"=aggr(Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks),Company) > 0"}>} distinct Company)

Re: Count companies (issue found)

maxsheva — Fri, 28 Jul 2017 12:13:43 GMT

Thanks for reply.

I've tried such kind of aggregation but the result is not as expected.

If use your expression and select 02/01/2017 result is below

Product1 = 1

Product2 = 1

But if look at data will see both products are in Company1 and Company2 on this date

Re: Count companies (issue found)

sunny_talwar — Fri, 28 Jul 2017 12:28:19 GMT

May be this

Count({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>}DISTINCT Aggr(If(Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks) > 0, Only({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>}Company)), Company, Product))

Re: Count companies (issue found)

maxsheva — Fri, 28 Jul 2017 13:05:24 GMT

That what I need, super!

Thanks a lot Sunny

Re: Count companies (issue found)

sunny_talwar — Fri, 28 Jul 2017 13:07:19 GMT

No problem at all