Show all columns in calculated dimension

[trdandamudi]

I have a calculated dimension in a pivot table as below:

=Aggr(If(avg(Aggr(Sum(force)/2),Name,ID)) <=100,'Low',

    If(avg(Aggr(Sum(force)/2),Name,ID)) > 100 And avg(Aggr(Sum(force)/2),Name,ID)) <=300,'Medium',

       If(avg(Aggr(Sum(force)/2),Name,ID)) > 300,'High'))),Name,ID)

It is working fine and shows the columns as Low, Medium and High when I have values for each. If I have values only for Medium then it shows only "Medium" column.

The question is how can I show all the three columns all the time regardless if it has a value or not.

Example:

Wrong:

Low     Medium     High

               20

Correct:    <--- This is how it should display

Low     Medium     High

0           20             0

Is this possible ?

Thanks

[rubenmarin]

Hi Thirumala, maybe works using ValueList for the calculated dimension..

Dimension

=ValueList('Low', 'Medium', 'High')

Expression

If(ValueList('Low', 'Medium', 'High')='Low', Low_Expression,

If(ValueList('Low', 'Medium', 'High')='Medium', Medium_Expression,

  If(ValueList('Low', 'Medium', 'High')='High', High_Expression)))

[trdandamudi]

How would I derive my value list for the dimension ? Because my current dimension is as below:

=Aggr(If(avg(Aggr(Sum(force)/2),Name,ID)) <=100,'Low',

    If(avg(Aggr(Sum(force)/2),Name,ID)) > 100 And avg(Aggr(Sum(force)/2),Name,ID)) <=300,'Medium',

       If(avg(Aggr(Sum(force)/2),Name,ID)) > 300,'High'))),Name,ID)

Can I say:

=ValueList(

=Aggr(If(avg(Aggr(Sum(force)/2),Name,ID)) <=100,'Low',

    If(avg(Aggr(Sum(force)/2),Name,ID)) > 100 And avg(Aggr(Sum(force)/2),Name,ID)) <=300,'Medium',

       If(avg(Aggr(Sum(force)/2),Name,ID)) > 300,'High'))),Name,ID), 'Low'.'Medium','High') )    ????

Thanks

[rubenmarin]

Using ValueList the values for dimension are fixed, not depending of data so the conditions should be in each of the expressions, ie:

If(ValueList('Low', 'Medium', 'High')='Low',

    Count(If(avg(Aggr(Sum(force)/2),Name,ID)) <=100, Name),

...

I'm not sure of how should be the expressions, with a sample I can try to provide a tested answer, and maybe a better answer in performance terms.

[trdandamudi]

Here is the sample qlikview dashboard for testing. when you open the dashboard the chart shows only "High" column but i would like to see Low, Medium and High.

T !hanks

[rubenmarin]

Hi, I can't get it right, the totals columns give me wrong result, maybe you knowing the data can get the right expression for totals... seems it fails when there are more than one row for the aggregation.

The totals expression is the one that excutes when SecondaryDimensionality()=0.

Anyway, first try a performance test to check how is working with such expressions.

[trdandamudi]

I am trying to mimic to my original dashboard and it is not working the way I want. It doesn't line up the values in the Low, Medium and High but shows the Totals when I use Secondary Dimensionality =0. When I use Secondary Dimensionality = 1 then it shows the values in Low, Medium and High but there are no Totals. I think i am missing something here...

[trdandamudi]

Is there any other approach to resolve this ?

Thanks

[jagan]

Hi,

Try like this

Calculated Dimension: ValueList('Low', 'Medium', 'High')

=If(ValueList('Low', 'Medium', 'High') = 'Low',

Expression for low,

If(ValueList('Low', 'Medium', 'High') = 'Medium',

Expression for Medium,

If(ValueList('Low', 'Medium', 'High') = 'High',

Expression for High)))

Regards,

Jagan.

[trdandamudi]

I tried but no luck can you please show me on the attached file... I think I am doing something wrong.

Thanks

[PradeepReddy]

try something like this..

properties--> Presentation--> Missing Symbols(Replace '-' with '0')