Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated Dimension Reference

Folks,

     I have a table with 3 columns where the first is a calculated dimension, the second is an expressio (count related to dimension) and the third I need to test the values related to the dimension - column A ('Sinal') - and show different values.

tabel QV.png

for example : if Sinal (dimension calculated) = ' Crítico'  then Comments (expression) should be 'abc',

                    if Sinal = 'Regular' then Comments should be 'xyz'

I don't know how  make reference to the column A ('Sinal'). I've already tried column () function, but it returns only columns related to expression, it means (columns B (Quantidade) and C (Comments).

    i.e: if (column(1) = 'Critico', 'abc', 'xwz') - Doesn't work!

6 Replies
Anonymous
Not applicable
Author

What expression did you use to generate the calculated dimension ? Should be able to add as an expression as well.

Jonathan

Not applicable
Author

Expression in the dmension :

= if(

aggr(sum (Qtde) /
(
    sum ({$<Ano_Mes = {$(=Max(Ano)&num(Max(Mes),'00') )} >}[Expectativa Vendas])
       *  num(MaxString([Mês Filtro]))), PartNumber) <=Var_Cor_Penetracao_Amarelo,
  
dual('Crítico',1)  ,

   if(
  
   aggr(sum (Qtde) /
(
    sum ({$<Ano_Mes = {$(=Max(Ano)&num(Max(Mes),'00') )} >}[Expectativa Vendas])
       *  num(MaxString([Mês Filtro]))), PartNumber) <=Var_Cor_Penetracao_Laranja ,
     
dual('Regular',2),

if(
aggr(sum (Qtde) /
(
    sum ({$<Ano_Mes = {$(=Max(Ano)&num(Max(Mes),'00') )} >}[Expectativa Vendas])
       *  num(MaxString([Mês Filtro]))), PartNumber) <=Var_Cor_Penetracao_Marinho,
      
dual('Bom',3),
if(
aggr(sum (Qtde) /
(
    sum ({$<Ano_Mes = {$(=Max(Ano)&num(Max(Mes),'00') )} >}[Expectativa Vendas])
       *  num(MaxString([Mês Filtro]))), PartNumber) <=Var_Cor_Penetracao_Verde,
      
dual('Muito Bom',4),

  if(aggr(sum (Qtde) /
(
    sum ({$<Ano_Mes = {$(=Max(Ano)&num(Max(Mes),'00') )} >}[Expectativa Vendas])
       *  num(MaxString([Mês Filtro]))), PartNumber)>Var_Cor_Penetracao_Verde,
      
dual('Excelente',5)



)
)
)
)
)

Anonymous
Not applicable
Author

Ouch, that's a pretty heavy expression to have in a calulated dimension. I'd be surprised if this didn't impact on performance. Either way, this may be too complicated to extract in an expression.

Have a look in the reference manual for 'Synthetic Dimension Functions', section 68.5 on page 809 in v11's manual. You may be able to use the valuelist() function.

"In charts with a synthetic dimension created with the valuelist function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuelist function with the same parameters in the chart expression"

Jonathan

montero91
Creator
Creator

Hi,

Within the script, insert a calculated field


if (Sinal = 'Critical', 'abc', 'XWZ') as Comments,

Thereby generate a new field and the calculated value, which should work as dimension.

I hope to help you.

sarahallen1
Creator II
Creator II

Do you have any suggestions when the calculated dimension is AGGR and doesn't work the same way as an expression (as above) but when a value list is not suitable?

I've attached an example QVW showing a problem I'm working on.  Please read the notes in the text boxes to see what my problem is and what I've tried.

I want a 'data availability check' type of thing, which is used by other expressions to give warnings.  I tried to put the check as an expression but it doesn't work as it needs to ignore one of the dimensions (my set analysis here doesn't work as expected with <FieldToIgnore=> because I think it's in the dimension).  But then when it's a calculated dimension instead with aggr, I can't refer to it in the expressions.

I know that putting the check field or something in the script would be recommended but my data model is a lot more complicated than what I've put in this example (sorry I can't share the real example for privacy reasons) and I'd really like to achieve it in the front end if possible.

Thanks in advance for your help!

sarahallen1
Creator II
Creator II

stalwar1‌ has answered my question here (the solution was not to use a calculated dimension so it might not help Gutemberg out, but it helped me!) https://community.qlik.com/message/1072137?et=watches.email.thread#1072137