Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
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
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.
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
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.
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...
Is there any other approach to resolve this ?
Thanks
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.
I tried but no luck can you please show me on the attached file... I think I am doing something wrong.
Thanks
try something like this..
properties--> Presentation--> Missing Symbols(Replace '-' with '0')