Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 ?
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
- Marcus
Thanks a lot for the details.
Your expression is returning '-' for all rows... I don't know whats happening...
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
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
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
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.