Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maxsheva
Creator II
Creator II

Count companies (issue found)

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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?

maxsheva
Creator II
Creator II
Author

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.

Kushal_Chawda

this seems to be working

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

maxsheva
Creator II
Creator II
Author

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

sunny_talwar

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

maxsheva
Creator II
Creator II
Author

That what I need, super!

Thanks a lot Sunny

sunny_talwar

No problem at all