topic Re: Returning text based on difference in Desktop

Returning text based on difference

UK_User123456 — Mon, 08 Jul 2019 11:12:19 GMT

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	Exisiting
12/05/2019	01/07/2019	50	New
01/06/2018	31/10/2018	152	Old

I have tried nested if statements but all I get is that for every customer we have it displays the same text for every date we have, I just want it to return the text based on the records I need.

TIA

Re: Returning text based on difference

jdbuchanan71 — Mon, 08 Jul 2019 13:09:36 GMT

Based on your example and assuming [Date Difference] is a measure this should work.

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

Re: Returning text based on difference

UK_User123456 — Mon, 08 Jul 2019 13:37:00 GMT

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

Re: Returning text based on difference

jdbuchanan71 — Mon, 08 Jul 2019 13:49:19 GMT

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

Re: Returning text based on difference

UK_User123456 — Mon, 08 Jul 2019 14:03:38 GMT

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.

Re: Returning text based on difference

tex628 — Mon, 08 Jul 2019 14:13:48 GMT

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!

Re: Returning text based on difference

jdbuchanan71 — Mon, 08 Jul 2019 14:42:02 GMT

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"
)

Re: Returning text based on difference

evandrocunico — Mon, 08 Jul 2019 18:02:52 GMT

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.

Re: Returning text based on difference

UK_User123456 — Tue, 09 Jul 2019 12:00:37 GMT

evandrocunico many thanks, will try this out. I have created a new column already for date diff and this throws out some random numbers, but I think I know where the issue lies.

I will respond once I have had the chance to try it out.