Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]
)
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.
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.
The invisible solution does not work. Thank you for the idea.
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.
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
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.