MultiValue Error

[Former Member]

MultiValue error strikes again.

This time I am trying to count number of CaseIDs where each case's status moved from "Pending Close" to "Open".

In idea world, each case's status should move from Open to Pending Close and then Close. However, I find that for some cases status changes back and forth from "Open" to "Pending Close" and I am trying to count distinct number of such cases.

Each case's status has StatusStartDate and StatusEndDate.

So far, I managed to find all cases affected by using combination of RunningCount and RunningSum functions, but as soon as I remove some of the columns from my report, to see total number of cases per day or just total number of cases, either it does not work (get blank) or I get Multivalue error.

All objects come from single query.

Below is a formula that finds those cases affected by the requirement I explained above:

If(RunningSum(RunningCount([Status Code];([Case Number])) Where ([Status Code] = "Open");([Case Number]))   >  RunningSum(RunningCount([Status Code];([Case Number])) Where ([Status Code] = "Pending Close");([Case Number])) Where ([Status Code] InList ("Open";"Pending Close")) ; 1 ; 0 )

how to list all caseID that meet the above?

= [Case Number] Where (RunningSum(RunningCount([Status Code];([Case Number])) Where ([Status Code] = "Open");([Case Number])) >RunningSum(RunningCount([Status Code];([Case Number])) Where ([Status Code] = "Pending Close");([Case Number])) Where ([Status Code] InList ("Open";"Pending Close")))

gives me Multivalue Error.

[former_member188911]

Hi Rafal,

it would be useful to know what SP/patch level you are on.

You should test on latest patch level,  if the error persists  you should start from a less complex scenario and increase complexity  until you find what is breaking it

thanks

Simone

[Former Member]

Ok, so I think I was doing something wrong and now I get all zeros instead of Multivalue Error.

and I think I found the answer, the problem is that as soon as I remove all objects from the table on which the formula depends, then the sorting changes, and it reverts back from sorting on Date (formula depends on Status Start Date) to sorting alphpabeticaly on Status Code.

I think that I could make it work if only I could code this part:

  RunningSum(RunningCount([Status Code];([Case Number])) Where ([Status Code] = "Open");([Case Number])

in SQL.

Does anybody know how to code Running Sum of Running Count (I think Running Count will be Row Number or Rank function in SQL) in SQL conditionally? (when status code = Open )

I can't affect sorting in the formula once I remove objects from my table, so the only solution left is to do this in SQL. I can do the row number in SQL but don't know how to do Running Total of that row number and also how to do this only when particular condition is met (status code equals to Open)