Calculated Column help - Nested If - says type mismatch Ugh!

[wethernt]

Can't figure out what I am doing wrong for the life of me... Yes, it long... trying to bucket dates into 8 different buckets as follows: 2013, 2014, >120days&YR15, 91-120, 61-90, 31-60, 1-30, Current Thanks for any help.


=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
IF([Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")))))))

 

[PentaGalCXO]

To track down the "type mismatch" I suggest you create another calculated column and try each row of your nested IF()s.

=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013","this one ok")

do this for each row... best guess is [Invoice Due Date] is not of type "Date"...

 

[PentaGalCXO]

missing parenthesis in DateValue???
should be DateValue("12/31/2013")...

 

[PentaGalCXO]

i mean missing quotes... too quick on the trigger finger this morning...

 

[wethernt]

To track down the "type mismatch" I suggest you create another calculated column and try each row of your nested IF()s.

=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013","this one ok")

do this for each row... best guess is [Invoice Due Date] is not of type "Date"...

Ok, this I did and each individual statement now works... I had to put quotation marks around the 12/31/20xx values for those individual statement to work. Funny thing is, when I go back to the nested if statement and put the quotes in place around the 12/31/20xx I get a different error that is related to the "120+YR15" that says "Cannot convert value '120andYR15' of type Text to type Integer." Here is my formula now...

=IF([Invoice Due Date]<=DATEVALUE("12/31/2013"),"2013",
IF([Invoice Due Date]<=DATEVALUE("12/31/2014"),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE("12/31/2015"), [Invoice Due Date]<(TODAY()-120)),"120andYR15",
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91to120",
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61to90",
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31to60",
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1to30",
"Current")))))))

So, I look at the format of the individual calculated columns and see that it is Auto (Text) for each of them. My nested calculated column was set to Data Type; Integer, Whole number. So the mismatch error (and the need for the quote detailed above) seems to have fixed everything when I changed my calculated column data type to Auto (Text). Thanks much for all the help. Only question at this point is about my "bands" in a separate reply here but, as for nesting and calculated column, I guess this now works. THANKS ALL.

 

[Matt Allington]

I don't see the image on you earlier post about the banding. Can you post your banding formula and an image of your banding table?

 

[wethernt]

I don't see the image on you earlier post about the banding. Can you post your banding formula and an image of your banding table?

Probably a forum newbie error but I did / thought I did post an image in the previous message. I see a little box icon and right click it then select 'open in new tab'.
here is the url for the image but I will try to insert again.

https://drive.google.com/file/d/0B7ikW2xFwK24YkQ5VU5QY283c0k/view

 

[Matt Allington]

Well without even looking at the formula, it seems to me that you are trying to write the banding formulas as Measures. They should be Calculated Columns on the InvoiceTable. Try that first