topic Re: Calculated Dimension Reference in QlikView

Calculated Dimension Reference

Fri, 14 Dec 2012 11:58:29 GMT

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.

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!

Re: Calculated Dimension Reference

Anonymous — Fri, 14 Dec 2012 12:26:18 GMT

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

Jonathan

Re: Calculated Dimension Reference

Fri, 14 Dec 2012 12:41:41 GMT

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)

)
)
)
)
)

Re: Calculated Dimension Reference

Anonymous — Fri, 14 Dec 2012 12:48:35 GMT

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

Re: Calculated Dimension Reference

montero91 — Fri, 14 Dec 2012 19:52:55 GMT

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.

Re: Calculated Dimension Reference

sarahallen1 — Tue, 28 Jun 2016 14:57:23 GMT

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!

Re: Calculated Dimension Reference

sarahallen1 — Tue, 28 Jun 2016 15:30:19 GMT

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