Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MVP
MVP

Re: Count Zeros by Set Analysis or If Statement

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

Could you upload a small sample QVW?

vinieme12
Esteemed Contributor II

Re: Count Zeros by Set Analysis or If Statement

Try as below

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

farrukh_shaikh
Contributor II

Re: Count Zeros by Set Analysis or If Statement

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

farrukh_shaikh
Contributor II

Re: Count Zeros by Set Analysis or If Statement

Hi Stefan,

I attached the sample data at Sunny's reply.

Can you please have a look.

Farrukh

MVP
MVP

Re: Count Zeros by Set Analysis or If Statement

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.

farrukh_shaikh
Contributor II

Re: Count Zeros by Set Analysis or If Statement

here you go! sample data is attached!

Farrukh

farrukh_shaikh
Contributor II

Re: Count Zeros by Set Analysis or If Statement

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)

View solution in original post

MVP
MVP

Re: Count Zeros by Set Analysis or If Statement

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.

Highlighted
payalgosar
Contributor II

Re: Count Zeros by Set Analysis or If Statement

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))

amanmishra
New Contributor II

Re: Count Zeros by Set Analysis or If Statement

hi Farrukh shaikh,

this should work for you

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