Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
That should be possible as long as your variable gets expanded to a field name. Try like:
Count( {< '$(sum_Sales)'={">0"}>} [Month])
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
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)
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
You mean Count( {< '$(sum_Sales)'={">0"}>} Month([Date-TempDate])) ? This dont work too.
[Date-TempDate] - field with date from master calendar
"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?
Also tried Sum(if($(sum_Sales)>0,1,0) - dont work. I guess problem is the same as above.
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
This dont work too:(
Count({< YearsStart([Date-TempDate]) = {"=sum(Eur)>0"}>} MonthsStart([Date-TempDate])