topic Re: Show all columns in calculated dimension in QlikView

Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 17:57:12 GMT

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

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

Low Medium High

0 20 0

Is this possible ?

Thanks

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 17:59:04 GMT

Here is the updated example:

Example:

Wrong:

Medium

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

Low Medium High

0 20 0

Thanks

Re: Show all columns in calculated dimension

nagireddy_qv — Tue, 15 Mar 2016 18:30:35 GMT

Hi,

Please make sure the below option is unchecked.

Dimensions -- Supress when value is Null.

Re: Show all columns in calculated dimension

Digvijay_Singh — Tue, 15 Mar 2016 18:40:57 GMT

Check Show all values as well.

Re: Show all columns in calculated dimension

johnw — Tue, 15 Mar 2016 18:42:26 GMT

Probably not the "right" solution, and not even positive it would work, but how about using 0 as your null and missing symbols on the presentation tab o the chart?

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 18:50:37 GMT

No, It is not working.. Thanks

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 18:51:13 GMT

It is already unchecked... Thanks

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 18:51:26 GMT

it is greyed out... Thanks

Re: Show all columns in calculated dimension

Digvijay_Singh — Tue, 15 Mar 2016 19:10:28 GMT

right, I realized it doesn't appear active for calculated dimension.

Can you share your expression? One confusing point is on one side we want to show dimension based on condition result and contradicting to that we want to show all dimensions.

I was thinking Valuelist as a synthetic dimension can help here bcz that way all constant dimensions can be shown all the time and measures can be calculated in expression using Pick(match(Valuelist().

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 19:18:21 GMT

Below are the three expressions I am using:

Exp1:

# of Trips =Count(Name)

Exp2:

Used = Avg(Aggr(count({<Record={'XXX'}>} DISTINCT [Tripsused]),Name,ID))

Exp3:

Per_Day = avg(Aggr(Sum(force)/2),Name,ID))

Thanks

Re: Show all columns in calculated dimension

rubenmarin — Tue, 15 Mar 2016 19:22:54 GMT

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)))

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 19:40:59 GMT

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

Re: Show all columns in calculated dimension

rubenmarin — Tue, 15 Mar 2016 19:55:13 GMT

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.

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 20:25:49 GMT

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

Re: Show all columns in calculated dimension

rubenmarin — Tue, 15 Mar 2016 21:12:25 GMT

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.

Re: Show all columns in calculated dimension

trdandamudi — Tue, 15 Mar 2016 23:46:41 GMT

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...

Re: Show all columns in calculated dimension

trdandamudi — Wed, 16 Mar 2016 02:35:07 GMT

Is there any other approach to resolve this ?

Thanks

Re: Show all columns in calculated dimension

jagan — Wed, 16 Mar 2016 02:45:19 GMT

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.

Re: Show all columns in calculated dimension

trdandamudi — Wed, 16 Mar 2016 03:15:26 GMT

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