9 Replies Latest reply: Jun 5, 2018 11:22 AM by Глеб Аитов

# Count Dimension only if Measure is positive

Trying to make simple calculation, tried both of below formulas, neither worked (still counts brands with Qty=0), please, help:

1. if(sum( [SalesQty]) > 0, count( distinct [Brand]),0)

2. count(

aggr(sum( {<SalesQty={">0"}>} Qty, Brand)

)

• ###### Re: Count Dimension only if Measure is positive

may be like this

count(

aggr(sum( {<SalesQty={">0"}>} Qty), Brand)

)

• ###### Re: Count Dimension only if Measure is positive

Hi

or maybe this :

sum(if(aggr(sum(SalesQty),Brand) >0,1))

• ###### Re: Count Dimension only if Measure is positive

the result includes 0 values

• ###### Re: Count Dimension only if Measure is positive

let's try this expression below:

count( {< Brand = {"=Sum(SalesQty)>0"} >} distinct Brand )

Hope it helps.

• ###### Re: Count Dimension only if Measure is positive

Maybe this

Count(if(Aggr(sum( [SalesQty]),Brand)>0,Brand))

• ###### Re: Count Dimension only if Measure is positive

How about this

Count(DISTINCT {<Brand = {"=Sum([SalesQty]) > 0"}>} [Brand])

• ###### Re: Count Dimension only if Measure is positive

the result returns "-"

• ###### Re: Count Dimension only if Measure is positive

QlikView and Qlik Sense are case sensitive, have you checked if the field names are right?

• ###### Re: Count Dimension only if Measure is positive

yes

though I suspect the problem is in ""

my actual formula is more complicated:

Count(DISTINCT {<[Производитель]=

{"=sum({<[Показатель]={'Остатки'},[Календарь Тип]={'CP'},[Склад Наименование]={'Оптовый интернет-магазин'},Дата={"\$(=date(RangeMin(\$(vReportDate),max(Дата))))"}>}[Количество товара])>0"}>}

[Производитель])

...and the bold part is highlighted with color:

I recall that in some cases I need to replace " with 'char' function?

• ###### Re: Count Dimension only if Measure is positive

Yes, just replaced the date with the fixed value '04.06.2018', and it now works.

Thank you

By the way, what is faster: use IF (like some of the guys proposed here) or use SET ANALYSIS?

• ###### Re: Count Dimension only if Measure is positive

Set analysis is faster than if statement. For the issue.. try this

Count(DISTINCT {<[Производитель]=

{"=sum({<[Показатель] = {'Остатки'}, [Календарь Тип] = {'CP'},[Склад Наименование] = {'Оптовый интернет-магазин'}, Дата = {""\$(=date(RangeMin(\$(vReportDate),max(Дата))))""}>} [Количество товара])>0"}>}

[Производитель])

I added two double quotes which will work as an escape sequence....

• ###### Re: Count Dimension only if Measure is positive

cool

single quotes also solved the issue

• ###### Re: Count Dimension only if Measure is positive

May be this -

count(if(sum( [SalesQty]) > 0, distinct [Brand]))

• ###### Re: Count Dimension only if Measure is positive

returns "-"

• ###### Re: Count Dimension only if Measure is positive

I suggest you to create Variable for that like and similar like below?

Variable

=date(RangeMin(\$(vReportDate),max(Дата)),'Your Дата Format Here')

And Then, Try this?

Count(DISTINCT {<[Производитель]=

{"=sum({<[Показатель]={'Остатки'},[Календарь Тип]={'CP'},[Склад Наименование]={'Оптовый интернет-магазин'},Дата={"\$(Variable)"}>}[Количество товара])>0"}>} [Производитель])

• ###### Re: Count Dimension only if Measure is positive

without creating a variable, i used single quotes and it solved the

problem.

Thank you