topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 06:06:37 GMT

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.

Re: Variable in Set Analysis

tresB — Mon, 01 Oct 2018 06:23:18 GMT

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

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 07:25:54 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 07:42:39 GMT

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)

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 07:49:16 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 07:56:57 GMT

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

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 08:01:41 GMT

"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?

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 08:03:24 GMT

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 08:14:40 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 08:14:47 GMT

This dont work too:(

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 08:18:02 GMT

Not necessary - you could try it with:

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

- Marcus

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 08:19:23 GMT

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 08:46:17 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 08:48:13 GMT

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 09:06:00 GMT

[$(=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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 09:24:01 GMT

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

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 09:37:17 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 10:14:41 GMT

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;)

Re: Variable in Set Analysis

marcus_sommer — Mon, 01 Oct 2018 10:47:29 GMT

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

Re: Variable in Set Analysis

Death4Free — Mon, 01 Oct 2018 12:17:19 GMT

App example and Data model