Skip to Content
author's profile photo Former Member
Former Member

MultiValue Error

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on May 21, 2013 at 08:56 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.