Count Dimension only if Measure is positive

[ziabobaz]

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)

)

[ziabobaz]

returns "-"

[ziabobaz]

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?

[ziabobaz]

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?

[sunny_talwar]

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

[Anil_Babu_Samineni]

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"}>} [Производитель])

[ziabobaz]

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

problem.

Thank you

[ziabobaz]

cool

single quotes also solved the issue