I need help calculating values bases on another column

[RvdHeijden]

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

[Anonymous]

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]

@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]

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

[RvdHeijden]

@Anonymous

Thanks for the help so far but i still get an error.

Function 'SWITCH' does not support comparing values of type True/False with values of type Tekst. Consider using the VALUE or FORMAT function to convert one of the values

I removed the ) in the formula except in the beginning and the end, but it still doesn't work. Im sure it's just a minor change.

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 · Accepted solution]

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";

...

...

[RvdHeijden]

@Anonymous

That did the trick !!! 

Thank you for your time and effort