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.

[Anil_Babu_Samineni]

May be this? I assume, your set operator working till here

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

May be use below to get more than 0 in report

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

[antoniotiman]

"quantity of Companies" (2) or Amount of times (4)?

[qv_testing]

May be this,

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

[maxsheva]

Thanks for reply.

I am looking for something like below

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

But unfortunately it is not working

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

and select any date it shows incorrect result

For example I select 05/01/2017 and it shows 0 but should be 2

As far as I see formula doesn't look at modificator [Date]={'>=$(=min([Date]))<=$(=max([Date]))'}

[maxsheva]

I need quantity of companies per date.

[maxsheva]

Thank you but I need count of distinct companies.

I am looking for something like below

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

But unfortunately it doesn't work

[antoniotiman]

Try this

=Count(DISTINCT Aggr(If(Sum(Stocks) > 0,Company),Company,Date))

You don't need Set Analysis because Date between Min and Max is already selected.

[effinty2112]

Hi Max,

I must be missing something but I don't see the point of the set expression

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

Try this:

=Count(Aggr(if(Aggr(Sum(Stocks),Company)>0,Company),Company))

Cheers

Andrew

[maxsheva]

I need Set Analysis because it looks to first date in range till current selected.

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