topic Re: Count Zeros by Set Analysis or If Statement in QlikView

Count Zeros by Set Analysis or If Statement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

Could you please advise me how to count ZEROs by set analysis or if statement.?

I am using the below statement but both of them did not work.

=count({<Sale={"0"}>} Sale)

=Sum(If(Sale =0,1))

Kind Regards,

Farrukh

Re: Count Zeros by Set Analysis or If Statement

swuehl — Tue, 06 Dec 2016 15:22:50 GMT

'Did not work' is not a good description of what you expect to see and what you got instead.

Could you describe your model, data, context of the expressions a bit more detailed?

For example, if Sale values are floating point values, they might differ slightly from zero.

Or are you looking for counting NULL?

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 15:30:08 GMT

Hi Stefan,

There are three dimensions

MonthYear

Region

Company

Expression

Sale with #,##0.000 format

Currently I'm only counting Zeros (0).

Farrukh

Re: Count Zeros by Set Analysis or If Statement

swuehl — Tue, 06 Dec 2016 15:36:15 GMT

Is "Sale" a field in your data model? How do its values look like?

Re: Count Zeros by Set Analysis or If Statement

sunny_talwar — Tue, 06 Dec 2016 15:38:41 GMT

May be like this:

Sum(Aggr(If(Sale'sExpressionHere = 0, 1, 0), MonthYear, Region, Company))

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 15:38:41 GMT

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 15:43:03 GMT

No Luck yet with the above statement.

Re: Count Zeros by Set Analysis or If Statement

sunny_talwar — Tue, 06 Dec 2016 15:44:25 GMT

There are no zeros in the above image that you have posted

Re: Count Zeros by Set Analysis or If Statement

swuehl — Tue, 06 Dec 2016 15:45:00 GMT

If Sale is a field in your data model and the above table shows its values, then there are no zero Sale values.

What do you expect to see?

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 15:45:39 GMT

Its a sample snapshot. there are zeros. in the selected month.

Re: Count Zeros by Set Analysis or If Statement

swuehl — Tue, 06 Dec 2016 15:55:33 GMT

I think I don't understand what you are talking about.

Could you upload a small sample QVW?

Re: Count Zeros by Set Analysis or If Statement

vinieme12 — Tue, 06 Dec 2016 16:00:42 GMT

Try as below

count({<company = {"=Sum(Sale)=0"}>}company)

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 17:01:32 GMT

Hi Sunny,

Please see the attached sample, there is a sum of last 12 months with a Cyclic Group, I tried all the statements as you guys suggested above.

I am looking to count only zeros and want to ignore null values.

Could you please advise me r !elated to this query?

Kind regards,

Farrukh

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Tue, 06 Dec 2016 17:03:18 GMT

Hi Stefan,

I attached the sample data at Sunny's reply.

Can you please have a look.

Farrukh

Re: Count Zeros by Set Analysis or If Statement

swuehl — Tue, 06 Dec 2016 17:05:47 GMT

Farrukh Shaikh wrote:

Hi Stefan,

I attached the sample data at Sunny's reply.

Can you please have a look.

Farrukh

Sorry, I can't see any attachment.

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Wed, 07 Dec 2016 09:08:47 GMT

here you go! sample data is attached!

Farrukh

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Wed, 07 Dec 2016 09:42:36 GMT

Hi Stefan,

I think its sorted. I used the below logic with set analysis, could you please advise me is it correct?

=Sum({$<Year=, Month=, Sale={"0"}, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale+1)

Re: Count Zeros by Set Analysis or If Statement

swuehl — Wed, 07 Dec 2016 09:49:28 GMT

Correct? Hard to tell without really knowing what you need to achieve.

If you want to count the records, I would probably write your expression like

=Count({$<Year=, Month=, Sale={"0"}, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)

Not sure if the YearMonth filter is correct, but if all your tests pass, then your expression is probably fine.

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Wed, 07 Dec 2016 10:24:49 GMT

Hi,

Is it that you want the sum of sales for the Month-Year & Region to be zero?

if so you can try this expression -

=sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>}

if(aggr(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale), MonthYear, Region)=0,1))

Re: Count Zeros by Set Analysis or If Statement

Anonymous — Wed, 07 Dec 2016 10:36:49 GMT

hi Farrukh shaikh,

this should work for you

=count(sales) - count({<sales = {'<>0'}>} sales)