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.

[marcus_sommer]

Not necessary - you could try it with:

Count({< [$(=maxstring([Sales_variant]))] = {'*'}>} [Month])

- Marcus

[Death4Free]

No result if($(sum_Sales)>0, count(MonthsStart([Date-TempDate]))) or something wrong in expression?

[marcus_sommer]

Your variable-creation isn't correct because you don't transferred the sum-expression to sum_sales else the expression-results of sum(Eur) respectively sum(Psc) - therefore change it to:

Variable $(sum_Sales) =

if(GetFieldSelections([Sales_variant]) = 'Eur', 'sum(Eur)',

if(GetFieldSelections([Sales_variant]) = 'Psc.', 'sum(Psc)'))

- Marcus

[Death4Free]

I dont understand this - [$(=maxstring([Sales_variant]))] = {'*'}, but tried this expression Count({< [$(=maxstring([Sales_variant]))] = {'*'}>} MonthsStart([Date-TempDate])) and it dont work.

[marcus_sommer]

[$(=maxstring([Sales_variant]))] returns the max. value of Sales_variant which is either the selected fieldvalue there or if is no selection the max. value. If we assume that there are just both above mentioned values it will without a selection return: [Psc] which is deduced from your other code a native field and this is checked by {'*'} if there are any existing records respectively values which are not NULL.

In many cases such a simplified construct will work because usually don't exists such NULL records and it's also rather unlikely that there are any respectively many negative fieldvalues which might lead unwanted to different results as querying if the sum(FIELD) > 0. If this is important you couldn't use this approach and must try it with an approach like: Count({< Category = {"=sum(Sales)>0"}>} [Month]) or depending on your datamodel with an if-loop like mentioned earlier.

Beside this it would be helpful if you describe what happens instead of it didn't work - are the result NULL, 0, any unexpected value. Further if you really want to use MonthsStart([Date-TempDate]) instead of a native field you will need a DISTINCT because otherwise it will further count the single dates.

- Marcus

[Death4Free]

Ok, I will sum up too what i tried:

1. Variable $(sum_Sales) was changed to

if(GetFieldSelections([Sales_variant]) = 'Eur', sum(Eur),

if(GetFieldSelections([Sales_variant]) = 'Psc.', sum(Psc)))

2. Count({< [$(=maxstring([Sales_varian]))] = {'*'}>} MonthsStart([Date-TempDate])) return Null for every year with distinct same

3. Count({< [$(=maxstring([Sales_varian]))] = {'*'}>} [Date-TempDate.autoCalendar.Month])) return 0 for every year

4. if($(sum_Sales)>0, count(distinct MonthsStart([Date-TempDate]))) return Null for every year

5. Count({< YearStart([Date-TempDate]) = {"=sum(Eur)>0"}>} MonthsStart([Date-TempDate])) return Null for every year

May be there is errors in syntax don know

[marcus_sommer]

1. the if-return is an expression and not a expression-string - it missed the single-quotes like: 'sum(Eur)'

2. the fieldname of Sales_variant missed the ending t - further you used monthsstart() instead of monthstart()

3. the fieldname of Sales_variant missed the ending t

4. you used monthsstart() instead of monthstart()

5. you used with yearstart() an expression as a fieldreference - further you used monthsstart() instead of monthstart()

- Marcus

[Death4Free]

1. If i change quotes like: 'sum(Eur)', it return string sum(Eur) not value

2. return 0 for every year

3. return 0 for every year

4. in year where was sales return total number of month, not month with sales, for other years null

5. Count({< [Date-TempDate.autoCalendar.Year] = {"=sum(Eur)>0"}>} distinct MonthStart([Date-TempDate]))

in year where was sales return total number of month, not month with sales, for other years 0

And by the way thank you for patience;)

[marcus_sommer]

Please provide some screenshots from your datamodel, objects and expressions maybe your issues could be detect in this way. Further helpful would be a small example-application whereby I have currently no Sense available and could only help by using View but there are people here which could help you.

- Marcus

[Death4Free]

App example and Data model