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

[Digvijay_Singh]

Dimension

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

Expression 1:

Pick(Match(ValueList('Low', 'Medium', 'High'),'Low', 'Medium', 'High'),

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

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

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

)

Expression 2:

Replace Count(Name) with Avg(Aggr(count({<Record={'XXX'}>} DISTINCT [Tripsused]),Name,ID)) in above exp 1

Similar for Expression 3, use avg(Aggr(Sum(force)/2),Name,ID)) in place of Count(Name)

Pl Correct in case any Bracket etc is missing.

[Kushal_Chawda]

You can optimize your expression like below

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

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

Now in expression do the below change

= alt(Expression,0)

Now go to chart properties->Presentation-> Uncheck Suppress missing -> or Uncheck Suppress zero

[trdandamudi]

I tried the first Expression and it is showing the values of 'Medium' also in the 'Low' Column. Attached is the dashboard that i was testing.

[Digvijay_Singh]

Sorry, I don't have license version show cannot open qvw, but

Are you ok with the formula logic, does it logically making sense to you? it can be corrected if the logic meets the purpose, The one I posted was draft one taken from the first post which looks like need modification -

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

So When Dimension Value is 'Low', it will check whether <Your above formula> has value <=100, which you were also linking with 'Low' Dimension' and for all such cases it will count  the Name.

I think avg(Aggr(Sum(force)/2),Name,ID))  should be enough to compare with <=100, not sure though

[trdandamudi]

Yes, avg(Aggr(Sum(force)/2),Name,ID))   is the correct expression. When I am using this it is showing the values of 'Medium' in 'Low' Column.

When i use the below expression:

Pick(Match(ValueList('Low', 'Medium', 'High'),'Low', 'Medium', 'High'),

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

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

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

)

Even though the value is > 100 and < 360 it is populating in 'Low' ?

[Digvijay_Singh]

Try like this may be -

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

[rubenmarin]

Hi Thirumala, sorry for late answer, I'm not having many free time this days...to explain a bit why my approach... I'm giving this options because I think the problem is that when you select a value, the calculated dimension isn't generating all the values, so no matter what you check, the dimension won't show values that aren't part of that dimension.

Another approachs can be (not tested, sorry again for adding more confussion):

- The optimal option, if possible, will be assign the Low, Medium or High value to each record in a new field in the script, and use this new field as dimension.

- Create an island table using Inline with the field having those three values (Low, med...) and use this as dimension checking the "show all values" mark. (you'll probably have to face the same problems than using ValueList)

Both options are based in the idea to have a field with all 3 values, some selections can hide them, but the values are in the field and we can play to show them.

Going back to my previous approach (wich also generates for sure the three values), it needs some kind of 4 expressions by each or your expressions:

- One for the total (secondaryDimensionality()=0)

- One for Low

- One for Medium

- One for High

So each expression has to have the expression for SecondaryDimensionality()=0 and SecondaryDimensionality()>0.

Hope this helps!

[suman_731995]

For SR 12, Legacy(qv 11), You can use following steps:

1. Limit the dimension frame/values (aggr() helps for this)

2. Create required expression

3. for all values of the dimension, add a expression as : count{1}  

4. hide bar/data points of this new exp. make it invisible.

5. select suppress null values option.

Hope that works! Thanks.