topic Re: Count companies in QlikView

Count companies

maxsheva — Tue, 25 Jul 2017 08:04:29 GMT

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.

Re: Count companies

Anil_Babu_Samineni — Tue, 25 Jul 2017 08:19:00 GMT

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)

Re: Count companies

antoniotiman — Tue, 25 Jul 2017 08:24:07 GMT

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

Re: Count companies

Nagaraju_KCS — Tue, 25 Jul 2017 08:35:11 GMT

May be this,

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 08:37:12 GMT

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]))'}

Re: Count companies

maxsheva — Tue, 25 Jul 2017 08:39:24 GMT

I need quantity of companies per date.

Re: Count companies

maxsheva — Tue, 25 Jul 2017 08:40:22 GMT

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

Re: Count companies

antoniotiman — Tue, 25 Jul 2017 08:43:53 GMT

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.

Re: Count companies

effinty2112 — Tue, 25 Jul 2017 08:52:54 GMT

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 08:54:01 GMT

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

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

Re: Count companies

Kushal_Chawda — Tue, 25 Jul 2017 08:56:07 GMT

try this

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

Re: Count companies

antoniotiman — Tue, 25 Jul 2017 08:57:59 GMT

OK (but I am not convinced)

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 08:58:24 GMT

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.

Re: Count companies

Kushal_Chawda — Tue, 25 Jul 2017 09:04:56 GMT

or this

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 09:06:28 GMT

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)

Re: Count companies

mangalsk — Tue, 25 Jul 2017 09:15:26 GMT

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 09:16:01 GMT

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)

Re: Count companies

Kushal_Chawda — Tue, 25 Jul 2017 09:26:03 GMT

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

Re: Count companies

maxsheva — Tue, 25 Jul 2017 09:35:28 GMT

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)

Re: Count companies

maxsheva — Fri, 28 Jul 2017 11:18:06 GMT

Hi Kushal,

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