Variable in Set Analysis

[Death4Free]

Hi! Is it possible to use variable as parameter in Set Analysis. I mean for example measure like this:

Count( {< $(sum_Sales)={">0"}>} [Month]) - dont work.

I want to count months where variable sum_Sales is more than 0.

[tresesco]

That should be possible as long as your variable gets expanded to a field name. Try like:

Count( {< '$(sum_Sales)'={">0"}>} [Month])

[marcus_sommer]

It's not quite clear what your variable contained but from your description I assume that it isn't a field else an expression. In this case I think you could try something like this:

Count({< Category = {"=sum(Sales)>0"}>} [Month])

     or maybe

Count({< Sales = {'*'}>} [Month])

- Marcus

[Death4Free]

This Count( {< '$(sum_Sales)'={">0"}>} [Month])  give me numbers but not that, what i expected. I will explain more details.

I want table :

Year     Count month with sales

2017     6

2018     3

Function above give me:

2017     365

2018     271

In other words count of dates.

Variable $(sum_Sales) =

if(GetFieldSelections([Sales_variant]) = 'Eur', $(sum_Eur),

if(GetFieldSelections([Sales_variant]) = 'Psc.', $(sum_Psc),0))

$(sum_Eur) = sum(Eur)

$(sum_Psc) = sum(Psc)

[marcus_sommer]

This means your month-field isn't a month else a date. It needs to be created with something like month(DateField) instead of just formatting a date like: date(DateField, 'MMM').

Edit: I just noticed that your variable contained other variables which contain an expression - this couldn't be used as a field-reference within the set analysis - it is just invalid and will be therefore ignored and you will get results as if no condition is applied. This means you need to adjust the above mentioned month-field issue and an approach by which you could apply your sum() condition against a field-reference. For this take a look on my both examples above.

- Marcus

[Death4Free]

You mean Count( {< '$(sum_Sales)'={">0"}>} Month([Date-TempDate])) ? This dont work too.

[Date-TempDate] - field with date from master calendar

[Death4Free]

"For this take a look on my both examples above." But this means that for every Sales_variant i need new measure:( Is it possible to do with one?

[Death4Free]

Also tried Sum(if($(sum_Sales)>0,1,0)  - dont work. I guess problem is the same as above.

[marcus_sommer]

In this case you nested aggregations without using aggr() which isn't valid. But with this you should get results:

if($(sum_Sales)>0, count(Month))

- Marcus

[Death4Free]

This dont work too:(

Count({< YearsStart([Date-TempDate]) = {"=sum(Eur)>0"}>} MonthsStart([Date-TempDate])