Show all calculated dimension values QV11SR1

[#1]

I have an issue where I cannot get the following expression to always display all possible values in my bar chart, regardless of selection:

aggr(
    FirstSortedValue(
        [Status],
        -[Status Update Date]
    ),
    [Opportunity ID]
)

Essentially, I need to see each possible status, always.  Therefore, if a particular selection (A) means that 4 out of 5 status have no relevant data or cause the expressions to become zero, then a zero or null value will show.  Ideally, if selection (A) is made, all 5 statuses will still show in the dimension.

I have tried:

- putting "{1}" and/or "TOTAL" in the expression (gives the wrong results, like a single null value),

- calculating the expression in a variable preceeded with "=" (causes Allocated Memory Exceeded error in object)

- every possible combination of "Show All Values" on the dimension, "Suppress Missing", and "Suppress Zero"

Thoughts?  Ideas?  Thank you for your help.

[cesaraccardi]

Hi,

Have you tried to put a set analysis expression in your FirstSortedValue function to ignore the selections in particular fields?

Example:

aggr(
    FirstSortedValue( {$<Status=>}
        [Status],
        -[Status Update Date]
    ),
    [Opportunity ID]
)

[swuehl]

What about adding another expression like

=count({1} Status)

and make that expression invisible? The set expression should force QV to show all dimension values.

[#4]

I have a workaround now by doing this aggregation in the script, but, obviously, it is not affected by selections to [Status Update Date].  That is not a problem in this particular application, but I have had problems in the past where sensitivity to selection would be important.  A solution would be wonderful if anyone can devise one.

[#5]

The invisible solution does not work.  Thank you for the idea.

[#6]

I tried play around with set analysis and other modifiers to the aggregation, to no luck.  I tried your specific suggestiong, and that also did not work.  Thank you for the idea.

[swuehl]

I have used something like I suggested above several times succesfully, for a sample file see attached.

Maybe I am missing something essential in your setting, so it might be helpful if you could post a sample that is close to your setting.

Regards,

Stefan

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