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: 
trdandamudi
Master II
Master II

Show all columns in calculated dimension

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


27 Replies
trdandamudi
Master II
Master II
Author

Here is the updated example:

Example:

Wrong:

    Medium    

        20

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

Low     Medium     High

0           20             0


Thanks

nagireddy_qv
Creator II
Creator II

Hi,

Please make sure the below option is unchecked.

Dimensions -- Supress when value is Null.Suppress.JPG

Digvijay_Singh

Check Show all values as well.

johnw
Champion III
Champion III

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?

trdandamudi
Master II
Master II
Author

No, It is not working.. Thanks

trdandamudi
Master II
Master II
Author

It is already unchecked... Thanks

trdandamudi
Master II
Master II
Author

it is greyed out... Thanks

Digvijay_Singh

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

trdandamudi
Master II
Master II
Author

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