14 Replies Latest reply on Apr 23, 2018 3:30 PM by JT Klepp

Date field seen as an integer

I have two date values from two sources that I am comparing to get revenue for the current fiscal year (starts 10/1).  I tried the following formula which I know works on a single dataset:

sum(IIF(MONTH([Date/Time])>=IIF(MONTH(DATE([Month Parameter]))>=10,DATE("10/1/"+str(YEAR([Month Parameter]))),DATE("10/1/"+str(YEAR([Month Parameter])-1))),IIF(MONTH([Date/Time])<=[Month Parameter],[Fees (USD)],NULL),NULL))

however, Tableaus is throwing an error on the red marks saying that I can't compare integer and date values. However, the [Date/Time] element and [Month Parameter] should both be dates, so I should be comparing two dates.  Any ideas?

• 1. Re: Date field seen as an integer

This Should Be Like:

(MONTH([Date/Time])<=Month([Month Parameter])

1 of 1 people found this helpful
• 2. Re: Date field seen as an integer

If You are Doing Date to date comparison Then This should work:

([Date/Time])<=[Month Parameter]

• 3. Re: Date field seen as an integer

MONTH([date])  returns an integer value from 1-through-12.  That's the root of the type mismatch.

Will all your date comparisons be within the same year?  (For instance, could the user pick January-2017 in the parameter, and you want to see where December-2016 falls with respect to the parameter value?)  If you are crossing a year in your comparison, 12 (December) will always be bigger than 1 (January) even though the example I gave should evaluate the other way around.

So the question and answer needs to clarify what you are actually doing here.

• 4. Re: Date field seen as an integer

So I solved the second part, as Month Parameter is Month and Year, not just Month, so by putting Month in front of [Month Parameter] the second part is fine:

..... <=Month([Month Parameter]),[Fees (USD)],NULL),NULL))

• 5. Re: Date field seen as an integer

If it worked, please close the Thread

• 6. Re: Date field seen as an integer

Hi Joe. No, the dates will cross a year. The Fiscal is from October 1 to September 30, so definitely crosses over on the year figure

• 7. Re: Date field seen as an integer

Still throwing an error on the first part of the equation

• 8. Re: Date field seen as an integer

what is that?

• 9. Re: Date field seen as an integer

Formula is now:

sum(IIF(MONTH([Date/Time])>=IIF(MONTH(DATE([Month Parameter]))>=10,DATE("10/1/"+str(YEAR([Month Parameter]))),DATE("10/1/"+str(YEAR([Month Parameter])-1))),IIF(MONTH([Date/Time])<=Month([Month Parameter]),[Fees (USD)],NULL),NULL))

the error of integer and date values comes on the first part of the equation in bold

• 10. Re: Date field seen as an integer

It's time to upload a workbook.

I don't see the need to do all the date parsing that you're doing.  I think you can simplify this a ton.  I would help you with that if I had a sample workbook to look at.

• 11. Re: Date field seen as an integer

I suspect something here, You have 2 Conditions

IIF(MONTH([Date/Time])>=IIF(MONTH(DATE([Month Parameter]))>=10

• 12. Re: Date field seen as an integer

Thanks for  the offer Joe. Unfortunately not able to due to the sensitive nature of the data

• 13. Re: Date field seen as an integer

maybe Deepak will figure something out for you.

But stuff like this is resolved so much better with an actual workbook.

• 14. Re: Date field seen as an integer

A lot of fiddling around here, but seems like this works:

sum(IIF(DATETRUNC('month',[Date/Time])>=IIF(MONTH(DATE([Month Parameter]))>=10.............

This converts the first part into an integer by only looking at the month.  Thanks all for helping.