Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

I need help calculating values bases on another column

Goodday,

 

I have a column which has StageNames for Orders such as 'Bid Made', 'Lost Order', 'Order', 'Project Withdrawn' etc.

i want to show these StageNames in a certain order in a Stacked Column Chart however BI goes alphabetical and i want the controle of the order.

My thought was that i use a formula that puts a number in front of the StageName so that BI will put them in the correct order.

 

This is my formula but it doesn't seem to work because i have to many arguments for the IF function, i tried 'value', 'Calculate' and switch but no effect.


Can someone tell me what i'm doing wrong ?

 

StageName2 = IF(Project[StageName] = "Suspect" ; "1.Suspect";
IF(Project[StageName] = "Prospect"; "2.Prospect";
IF(Project[StageName] = "RFI received (Qualify)"; "3.RFI received (Qualify)";
IF(Project[StageName] = "Qualified"; "4.Qualified");
IF(Project[StageName] = "RFQ received"; "5.RFQ received");
IF(Project[StageName] = "Bid Made"; "6 Bid Made");
IF(Project[StageName] = "Order"; "7.Order");
IF(Project[StageName] = "Lost Order"; "8.Lost Order");
IF(Project[StageName] = "No Bid"; "9.No Bid");
IF(Project[StageName] = "Project Withdrawn"; "10.Project Withdrawn");
"No stageName")))

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Your first argument to SWITCH is what will be evaluated. You should ONLY use Project[StageName].

    Then your values is what will be compared to your first argument. So in your code remove = "Suspect" and instead start the formula like this:

    StageName2 = Switch(Project[StageName];

    "Suspect";"1.Suspect";

    ...

    ...

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    For this purpose I think you should use Switch instead, you say you have tried but maybe you got it wrong.

     

    Try the following code instead:

     

    StageName2=SWITCH([Project[StageName],

    "Suspect","1.Suspect",

    "Prospect","2.Prospect",

    "RFI received (Qualify)","3.RFI received (Qualify)",

    ...

    )

    • RvdHeijden's avatar
      RvdHeijden
      Icon for Post Prodigy rankPost Prodigy
      9 years ago

      Anonymous

      I tried your formula but now it gives a syntax error which i can't find.

      I have to use ; instead of the , but even i change the error remains the same

       

      StageName2 = Switch(Project[StageName] = "Suspect" ; "1.Suspect";
      "Prospect"; "2.Prospect";
      "RFI received (Qualify)"; "3.RFI received (Qualify)";
      "Qualified"; "4.Qualified");
      "RFQ received"; "5.RFQ received");
      "Bid Made"; "6 Bid Made");
      "Order"; "7.Order");
      "Lost Order"; "8.Lost Order");
      "No Bid"; "9.No Bid");
      "Project Withdrawn"; "10.Project Withdrawn");
      "No stageName"))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        In your code there seems to be a few ) that shouldn't be there.

        Only use Switch().

        all other () should be inside your text-quotes "" as in example "RFI received (Qualify)"