Using operator "+" in THEN clause of IF formula

[zoomol]

Hi All,

I'm having this IF formula:

IIF(IsEmpty([Purchased]),
"Beyond Country Lifecycle",

IIF(DateTimeDiff([Purchased],[Report Date],"months")*-1<=6,
"6 First Months",
IIF(DateTimeDiff([Purchased],[Report Date],"months")*-1>6 AND DateTimeDiff([Purchased],[Report Date],"months")*-1<=12,
"Second 6 Months",
IIF(DateTimeDiff([Purchased],[Report Date],"months")*-1>[Refresh Cycle # of Months]+12,
"1 Year Beyond Country Lifecycle",
IIF(DateTimeDiff([Purchased],[Report Date],"months")*-1>[Refresh Cycle # of Months],
"Beyond Country Lifecycle",
IIF([Refresh Cycle # of Months]-DateTimeDiff([Purchased],[Report Date],"months")*-1<=6,
"Up To 6 Months Life Left",
IIF([Refresh Cycle # of Months]-DateTimeDiff([Purchased],[Report Date],"months")*-1>6 AND [Refresh Cycle # of Months]-DateTimeDiff([Purchased],[Report Date],"months")*-1<=12,
"Up To 12 Months Life Left", "Year "+DateTimeDiff([Purchased],[Report Date],"years")*-1+1)))))))

It is not working, since the last "FALSE"

"Year "+DateTimeDiff([Purchased],[Report Date],"years")*-1+1

argument .

The build-in debugger says, that I have "Type mismatch in operator +."

I dag a lot of community topics and google, and the answer was always the same: use "+" (Addition) operator to relate string with variable... But, what if I'm using a calculation against the variables? And... I haven't found a solution.

So, how to relate a string "Year " with the calculation of DateTimeDiff([Purchased],[Report Date],"years")*-1+1.

Please help.

Thanks in advance

Jarek

[Thableaus]

Hi @zoomol 

Use ToNumber function.

ToNumber([Year]) +....

If the final result should be a string, then ToString(....) function should be used with the whole result.

ToString(ToNumber([Year]) +.....)

Cheers,

[patrick_digan · Accepted solution]

@zoomol Try using tostring:

"Year "+ToString(DateTimeDiff([Purchased],[Report Date],"years")*-1+1)

[patrick_digan · Accepted solution]

@zoomol For what it's worth, here would be another way to basically write the same formula that you have:

1) Create a double field called Months. Since you use it so many times, it's easier to write the formula once.

Months: DateTimeDiff([Report Date],[Purchased],"months")

2) Change your formula to this (minus any typos I've made):

IIF(IsEmpty([Purchased]),
"Beyond Country Lifecycle",

IIF([Months]<=6,

     "6 First Months",

IIF([Months]<=12,

     "Second 6 Months",

IIF([Months]>[Refresh Cycle # of Months]+12,

     "1 Year Beyond Country Lifecycle",

IIF([Months]>[Refresh Cycle # of Months],

     "Beyond Country Lifecycle",

IIF([Months]>[Refresh Cycle # of Months]-6,

     "Up To 6 Months Life Left",

IIF([Months]>[Refresh Cycle # of Months]-12,

     "Up To 12 Months Life Left", "Year "+ToString([Months]/12+1))))))))

[zoomol]

patrick_digan THANK YOU! You made my life easier... I was trying many things, but as they say "it is darkest under the lantern"... Forgot to treat everything as string :) Excel is less picky here :P