topic Re: Error in calculated dimension in QlikView

Error in calculated dimension

Wed, 26 Aug 2015 15:44:54 GMT

Hello,

I am trying to use specific field values in my chart dimension. I only want to use three out of one hundred. This is what I have for the Calculated Dimension right now, but it is erroring out:

=Only({$<descriptionMeasure={"*Total Net Interest*","*Other Income*","*Total Revenue*"}>}descriptionMeasure)

How can I select just three fields out of many to display as my chart dimension?

Thanks,

Frank

Re: Error in calculated dimension

Qrishna — Wed, 26 Aug 2015 15:50:44 GMT

did you aggregate with respect to other dimensions of the chart?

try this:

AGGR(Only({$<descriptionMeasure={"*Total Net Interest*","*Other Income*","*Total Revenue*"}>}descriptionMeasure),OtherTableDimensions)

Re: Error in calculated dimension

Wed, 26 Aug 2015 16:02:28 GMT

There are no other dimensions to the chart. I want to use descriptionMeasure for the dimension, but I don't want all the values from descritpionMeasure, I want three specific ones. This chart will always be the same three descritpionMeasure fields and will cahnge according to region or business unit.

Re: Error in calculated dimension

Qrishna — Wed, 26 Aug 2015 16:14:05 GMT

AGGR(Only({$<descriptionMeasure={"*Total Net Interest*","*Other Income*","*Total Revenue*"}>}descriptionMeasure),descriptionMeasure)

then try this. and let me know if that works

Thanks

krishna

Re: Error in calculated dimension

Wed, 26 Aug 2015 16:51:32 GMT

I think that the ONLY is not the right function to use. If you had a field that contained values but only wanted to use a few for a dimension, what would you do?

Re: Error in calculated dimension

Wed, 26 Aug 2015 16:55:24 GMT

Kind of like this in SQL

Select myFieldValue

from descriptionMeasure

where myFieldValue in ('Value1','Value 39','Value4237')

Re: Error in calculated dimension

Qrishna — Wed, 26 Aug 2015 17:06:15 GMT

For calculated dimensions, you have to use only() function, but should be aggregated.

As Only() is the only non aggregate function where we can use set analysis to filter the field.

if calculated dimension is the only dimension in the chart then the Expression will be Aggr(Only({Condition}FieldName),FieldName).

In your case:

Aggr(Only({$<myFieldValue = {'Value1','Value 39','Value4237'}>}myFieldValue)).

let me know if i answered your question.

Thanks

Re: Error in calculated dimension

Kushal_Chawda — Wed, 26 Aug 2015 17:40:34 GMT

try below if you want to display wild card match

aggr(if(wildmatch(descriptionMeasure,'*Total Net Interest*','*Other Income*','*Total Revenue*'),descriptionMeasure),descriptionMeasure)

or for exact pattern match try below

aggr(if(match(descriptionMeasure,'Total Net Interest','Other Income','Total Revenue'),descriptionMeasure),descriptionMeasure)

Re: Error in calculated dimension

sasiparupudi1 — Wed, 26 Aug 2015 17:46:19 GMT

Try Like this

=Aggr(Only({$<descriptionMeasure={'*Total Net Interest*','*Other Income*','*Total Revenue*'}>}descriptionMeasure),descriptionMeasure)

hth

Sasi