Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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?
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.
this seems to be working
=Count( {<[Date]={'>=(=min([Date]))<=$(=max([Date]))'},Company= {"=aggr(Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks),Company) > 0"}>} distinct Company)
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
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))
That what I need, super!
Thanks a lot Sunny
No problem at all