Refer to Another Dimension in a Dimension in Pivot Table

[#1]

I have a calculated dimension which has got an heavy expression in the form

pick(1+match(...,...,...),...,...,...,...)

with about 100 values in the match statement.

I would like to refer to it in another calculated dimension. This second calculated dimension is

=mid(Aggr( Concat(Year & '-' & pick(match(UPPER(Month),

'JAN','FEB','MAR','APR','MAY','JUN', 'JUL','AUG','SEP','OCT','NOV','DEC'), '01','02','03','04','05','06','07','08','09','10','11','12'),','),

MYFIELD),1,7)

With MYFIELD one of the fields in the data model, the computation is correct.

But if I replace MYFIELD by the expression for the calculated dimension pick(1+match(...,...,...),...,...,...,...) , result is Null() for all rows. In other words, with this expression for the second calculated dimension,

=mid(Aggr( Concat(Year & '-' & pick(match(UPPER(Month),

'JAN','FEB','MAR','APR','MAY','JUN', 'JUL','AUG','SEP','OCT','NOV','DEC'), '01','02','03','04','05','06','07','08','09','10','11','12'),','),

pick(1+match(...,...,...),...,...,...,...)),1,7)

result is Null() for all rows.

How can I refer to the first calculated dimension in this very case ? Is there a workaround ?

Thanks in advance

[marcus_sommer]

There isn't any possibility to refer to a calculated dimension - you could only wrap this calculated dimension in your expression - therefore your approach is generally right. But this could be very complicated and slow. Every return from any expressions-parts must look if they were written manually - especially by returning from list-strings or fieldnames.

For such cases is it often necessary to use $-sign expansion: $(=YourExpression()). I suggest you tried to simplify your expressions (e.g.: you didn't need your pick(match()) on month, you could use num(month, '00') because date-values are dual()) - and test it partly without a expression-label (the expression could be show null but the label will often show what qv tried to calculate).

- Marcus

[#3]

Thanks for your reply Marcus.

I am not familiar to $-sign expansion, and I do not understnad how to use it in this very case? Would you accept to explain me with an example how to use the $-sign expansion?

Also, do you mean that i can refer to an expression with its label in a calculated dimension ?

Thanks again

[#4]

Do You mean this:

=mid(Aggr( Concat(Year & '-' & pick(match(UPPER(Month),

'JAN','FEB','MAR','APR','MAY','JUN', 'JUL','AUG','SEP','OCT','NOV','DEC'), '01','02','03','04','05','06','07','08','09','10','11','12'),','),

$(=pick(1+match(...,...,...),...,...,...,...))),1,7)

? What does it change compared to previous version ?

[marcus_sommer]

I mean:

=mid(Aggr( Concat(Year & '-' &num(Month, '00'), $(=pick(1+match(...,...,...),...,...,...,...))),1,7)

You want return a field for the aggr - this could be very tricky and you will need many trials ... returning a valid value and give it back as [field] - perhaps it is an alternatively to calculate these in a variable.

For $-sign expansion look here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

$-Sign Expansions in QlikView

- Marcus

[#6]

Thanks a lot for the details.

Your expression is returning '-' for all rows... I don't know whats happening...

[#7]

Could you explain your comment inother words ? I do not understand what you suggest here.

You want return a field for the aggr - this could be very tricky and you will need many trials ... returning a valid value and give it back as [field] - perhaps it is an alternatively to calculate these in a variable.

Thanks for that

[#8]

Also, this simple expression for the calculated dimension does not work:

=mid(Aggr( Concat(Year,','),

$(=IF([Corporate Account]='OTHER',[Sales Account], [Corporate Account]))),

,1,4)

this time i get Error in Calculated Dimension

[marcus_sommer]

There are many things which could be wrong - therefore it needed very many trials ...

What worked is:

Aggr( Concat(Year,','), $(=IF([Corporate Account]='OTHER','[Sales Account]', '[Corporate Account]'))

or with an additionally variables:

Aggr( Concat(Year,','), $(var)) with var: =IF([Corporate Account]='OTHER','[Sales Account]', '[Corporate Account]')

Most often will be used a simply if-loop for such cases, often combined with dimensonality():

=IF([Corporate Account]='OTHER',

     Aggr(Concat(Year,','),[Sales Account]),

     Aggr(Concat(Year,','),[Corporate Account]))

- Marcus

[#10]

Thanks a lot! this is very helpful.

It is almost correct. Now, I would like to add conditions on whether to concatenate a given year or not: I append the year for a row if it is verifying conditions as in

Aggr( IF([Product Type]= 'Sales', IF(MasterCodeDesc = 'BASE', IF(Year>2009,Concat( Year,',')))),

$(=pick(1+match(...,...,....,...),...,....,...,...)))

and I get error in calculated dimension. Could you help me troubleshoot this last point ?

Many thanks again, it helped me a lot.