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

[Peter_Brunner]

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

[swuehl]

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

[Peter_Brunner · Accepted solution]

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