topic Re: Help! Variable with Set Analysis, Variable with if, results Into a field in QlikView

Help! Variable with Set Analysis, Variable with if, results Into a field

Peter_Brunner — Fri, 20 May 2016 02:49:34 GMT

Hi All,

I have been trying to get results into a Dimension field from 2 variables One Variable using set analysis then another using "If " on the first set analysis Variable My approach may be incorrect what is the correct way to achieve this? Have tried in Script Calculated Dimension with No results...

1st variable setup in Variable Overview

vClass = NUM(sum({$<TRANS_TYPE={'DRINV','DRCDT'},ThreeMthsFlag={'1'}>}ENTERED_QTY)/3*12/SL_ON_HAND_QTY,'###.##')

this returns data correctly in Straight table expression

Then the Second variable I have

IF($(vClass)>=0 and $(vClass)<=3, '0<= LOW <3',

IF($(vClass)>=3 and $(vClass)<=6, '6<= MED<9',

IF($(vClass)>=6 and $(vClass)<=9, '9<= HIGH <12',0)))

Now this works in an expression = $(vClassCount) in a straight table but I wish to use this on a Dimension in Graph

any Guidance Appreciated

regards Peter

Re: Help! Variable with Set Analysis, Variable with if, results Into a field

swuehl — Fri, 20 May 2016 10:01:46 GMT

A dimension should return something 1 dimensional, like a field, not a scalar like your expression variables.

You could maybe use advanced aggregation to create a calculated dimension, like

=Aggr( $(vClassCount), YourDimensionalFieldTGroupBy)

YourDimensionalFieldTGroupBy could be a (list of) field names, like CustomerID.

AGGR...

Pitfalls of the Aggr function

Re: Help! Variable with Set Analysis, Variable with if, results Into a field

Peter_Brunner — Fri, 27 May 2016 04:15:02 GMT

I worked out the best way to handle this is create a field by changing

e.g:

load * ,

if(match(TRANS_TYPE,'DRINV','DRCDT' and ThreeMthsFlag='1'),ENTERED_QTY)/3*12/SL_ON_HAND_QTY as vClass

Then loaded Field vClass again in another table with If statements to create data needed

thanks for your Reply Stefan it was Helpful !

Peter