Count companies

[maxsheva]

data:

LOAD * Inline [

Date, Company, Product, Stocks

01/01/2017, Company1, Product1, 4

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

03/01/2017, Company2, Product1, 2

04/01/2017, Company1, Product1, 3

05/01/2017, Company1, Product1, -2

05/01/2017, Company2, Product1, -1

06/01/2017, Company2, Product1, 2

];

Expression

Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks)

I need to find quantity of Companies where expression above is more then zero.

When to use filter "date" it should show relevant quantity.

Any help would be appreciated.

[Kushal_Chawda]

try this

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

[antoniotiman]

OK (but I am not convinced)

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

[maxsheva]

Hi Andrew,

Set Analysis looks to first date in range till current selected.

({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>}

In the formula below when I select any date it calculates all values of stocks from first date till selected

Sum({<[Date]={'>=(=min([Date]))<=$(=max([Date]))'}>} Stocks)

So I need count of Companies where stocks in range is more then 0

When I say range it means dates from 01/01/2017 till current selected.

[Kushal_Chawda · Accepted solution]

or this

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

[maxsheva]

As far as I see your expression should provide correct result but unfortunately not.

Can't understand why.

When I select 05/01/2017 it still shows 0

but should be 2 (Companies)

[mangalsk]

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

[maxsheva]

Thanks to all and special to Kushal!

Below is that what I was looking for

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

[Kushal_Chawda]

It's 0 because, sum of stock <=0

[maxsheva]

when I added 'distinct' and deleted dollar sign where 'min' I got what I need

thx

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

[maxsheva]

Hi Kushal,

I found one issue. Please look at this Count companies (issue found)