Forum Discussion

UK_User123456's avatar
UK_User123456
Resolver I
7 years ago
Solved

Returning text based on difference

Hi All,   Is there a way of returning text based on a number that I have got e.g. the difference between date(s)?   e.g.  Date 1 Date 2 Date Difference Text 01/01/2019 01/06/2019 151 ...
Need Help
  • evandrocunico's avatar
    evandrocunico
    7 years ago

    Hi UK_User123456 

     

    create a column:

     

    date diference = DATEDIFF(date1;date2;DAY)  (use semicolon or comma)
     
    create another column:
    text result = If (date diference <= 50;"New";date diference <= 150;"Existing";"Old")
     
    best regards.
UK_User123456's avatar
UK_User123456
Resolver I
7 years ago

jdbuchanan71  Thanks for this, but it then throws out the error of MdxScript(Model) (140,98), 'SWITCH' does not support comparing values of type True/False with values of type text. I dont have any columns or values that are of Ture/False types, so not sure why it wont accept the measure?

 

TIA

jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

UK_User123456 , You will need to share your model, the measure works in my test:

  • UK_User123456's avatar
    UK_User123456
    Resolver I
    7 years ago

    jdbuchanan71see below the screenshots. The data types should are the same, as there are no true/false. 

    I wanted to be able to say that if the "Date Difference is between a certain number it would return the text I wanted it to display. I have also attached the error message that I receive.

     

    • tex628's avatar
      tex628
      Community Champion
      7 years ago

      Can you share the nested IF statement that you tried? I dont see any reason as to why a nested if statement wouldnt work here! 

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      7 years ago

      Is your field 'Date Difference Last Gift (By Day)' a measure of a column from a table?

       

      If you want to change the compare you can change to operator in the measure.

      Text =
SWITCH(
    TRUE(),
    [Date Difference] < 50, "New",
    [Date Difference] < 151, "Existing",
    [Date Difference] >= 152, "Old",
    "Other"
)