Count Zeros by Set Analysis or If Statement

[Anonymous]

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

[swuehl]

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

Could you upload a small sample QVW?

[vinieme12]

Try as below

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

[Anonymous]

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

[Anonymous]

Hi Stefan,

I attached the sample data at Sunny's reply.

Can you please have a look.

Farrukh

[swuehl]

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.

[Anonymous]

here you go! sample data is attached!

Farrukh

[Anonymous · Accepted solution]

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)

[swuehl]

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.

[Anonymous]

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

[Anonymous]

hi Farrukh shaikh,

this should work for you

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