Forum Discussion

cheid1977's avatar
cheid1977
Advocate I
9 years ago
Solved

If Statement with two different data types

In the data set I have a column for delivery date. If there is no delivery date the source of the data enters "1/1/1900". I am trying to create a second column that leaves the cells blank if this dat...
Need Help
  • cheid1977's avatar
    cheid1977
    9 years ago

    I found that the solution is to go under "OPTIONS">Data Load, and uncheck "Automatically detect column types and headers for unstructured sources".  This automatically converts everything to text and allows you to select the data type for each column.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

What if I am trying to change a calendar date to a fiscal year date, e.g. Oct 1, 2021 is the first month of 2022.  Using the following formula, I get expresstions that yield variant data type cannot be used to dafine calculated columns.  

FiscalYear =
If(
CustDt[Fiscal Month Sort] <4,
right(year(CustDt[Date]) + 1),
CustDt[Date]
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try these calculated column formulas in the Calendar Table

    Month number = month(calendar[date])

    Fiscal Year = if(calendar[month number]>10,year(calendar[date])+1,year(calendar[date]))

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much, Ashish.  I appreciate your help.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        You are welcome.