Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Expressions that yield variant data-type cannot be used to define calculated columns.

Hello,  I tried to find out from the previous solutions from the forum. but I could not. I got that error. May you help me to reach the solution?   Column 3 = IF(Forecast[CSR_Remarks -Delivery F...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Wrap the 1, 2 and 3 in "" to make them text 🙂 

     

    Column 3 = IF(Forecast[CSR_Remarks -Delivery Forecast]="FOC Order","1",
    IF(Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled","2",
    IF(Forecast[CSR_Remarks -Delivery Forecast]=BLANK(),"3",
    IF(MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast","Future Forecast")
    )))
     
    Just as an aside you can use SWITCH to write nest IFs:
    Column 3 =
    Switch(
    TRUE()
    ,Forecast[CSR_Remarks -Delivery Forecast]="FOC Order", "1"
    ,Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled", "2"
    ,Forecast[CSR_Remarks -Delivery Forecast]=BLANK(), "3"
    ,MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast"
    ,"Future Forecast")
     
    The error is basically saying you cant have numbers and text in the same column
    By wrapping the numbers in "" or using the format function they become text like your text saying "Current forecast".
     
SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
1 year ago

Wrap the 1, 2 and 3 in "" to make them text 🙂 

 

Column 3 = IF(Forecast[CSR_Remarks -Delivery Forecast]="FOC Order","1",
IF(Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled","2",
IF(Forecast[CSR_Remarks -Delivery Forecast]=BLANK(),"3",
IF(MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast","Future Forecast")
)))
 
Just as an aside you can use SWITCH to write nest IFs:
Column 3 =
Switch(
TRUE()
,Forecast[CSR_Remarks -Delivery Forecast]="FOC Order", "1"
,Forecast[CSR_Remarks -Delivery Forecast]="Order to be cancelled", "2"
,Forecast[CSR_Remarks -Delivery Forecast]=BLANK(), "3"
,MONTH(Forecast[CSR_Remarks -Delivery Forecast])=MONTH(TODAY()),"Current Forecast"
,"Future Forecast")
 
The error is basically saying you cant have numbers and text in the same column
By wrapping the numbers in "" or using the format function they become text like your text saying "Current forecast".