I get a #MULTIVALUE for an own variable of type dimension.
Common behavior (?): According to my training and everything I have read, the typical behavior of a dimension object should be to display one distinct value per column (in tables with header on the left). For example if the current context is [month] and there are two incidents in April, then dragging the variable into the table should extend the context to [month];[Incident ID], resulting in the column for April being split into two columns, with values (April, Inc 1) and (April, Inc 2).
Unfortunately, that does not work in my case and I would like to know whether I do something wrong (and what) or it is improper behavior / a bug.
Background: I have a single data-provider returning a bunch of dimension-objects for every time in incidents' history, where an incident changed status into "Resolved". Incidents may be reopened several times and then reach the "Resolved" status, again. Due to lack of "Re-opened" status in history, an incident counts as "Re-opened", if there are at least 2 entries with status "Resolved" in its change history.
Important objects are:
- [Incident ID] - Unique ID for each incident
- [Create Date] - The creation date of the history record, where it was set to status "Resolved"
- [YearMonth] - Dimension, a string variable containing the year and month of [Incident Last Resolved Date] dimension from data-provider, in form "yyyy/MM". Due to duration of incident resolution procedure, it may differ from the latest [Create Date] and cannot be reliably used for comparison!
My approach: First I calculate the last resolved date of each incident and make it available in each record. I defined measure [__true_resolved_date] as:
= Max([Create Date] In ([Incident ID];[Create Date])) In ([Incident ID])
Then I created a variable [__reopened_incident] of type dimension object just to SEE all result variables in the table (counting on the common, splitting behavior of dimension objects). It contains an IF clause to return the Incident ID of all records, where the condition is met.
= If ([Create Date] <> [__true_resolved_date]) Then [Incident ID]
To get the count of all re-opened incidents, I just counted the number of Incident ID in [__reopened_incident], which works fine.
My problem is that after pulling this [__reopened_incident] variable into the table, it displays #MULTIVALUE for the single reopened Incident INC0123 in April.
Example what is displayed (incident Name changed)
YearMonth | 2015/04 | 2015/05
[__reopened_incident] | #MULTIVALUE |
[__true_resolved_date] | 04-04-2015 | 05-05-2015
Note: "05-05-2015" is the single resolved date of a single incident in May, not relevent for explanation
As soon as I drop the [Create Date] dimension into the table, too, the April column gets split:
YearMonth | 2015/04 | 2015/04 | 2015/05
[__reopened_incident] | INC0123 | |
[__true_resolved_date] | 04-04-2015 | 04-04-2015 | 05-05-2015
[Create Date] | 01-01-2014 | 04-04-2015 | 05-05-2015
What I'd expect, w/o having to add [Create Date] to the block:
YearMonth | 2015/04 | 2015/04 | 2015/05
[__reopened_incident] | INC0123 | |
[__true_resolved_date] | 04-04-2015 | 04-04-2015 | 05-05-2015
Using other comparison in the IF clause, like If ([Create Date] < ToDate("01012015";"ddMMyyyy") ... works perfectly fine and splits the column into two, one for INC0123 and one empty.
It also works when comparing to a (dummy) measure like If ([Create Date] < _comp_date ... with [_comp_date] is the measure =[Create Date].
If I set the table's display option "Avoid duplicate row aggregation" to True, I get two columns for April, but still a #MULTIVALUE in both columns for [__reopened_incident].
Does anyone have an idea why this happens? Any hint or idea is appreciated.
Regards Michael
PS: this is only to find out, why the column is not split up. I am not looking for another way to identify the "re-opened" status.
