To compare a date field less than or grater than12 months

[#1]

Hi,

I am trying to calculate a field to identify the status of the activity.

If the activity date is less than 12 months, then it is Active and if the activity date id greater than 12 months, then it is Inactive.

For example -

if(match([Type], 'Usable')=0 and [Activity Date]<365, 'Active', 'Inactive') as Active_Status

Any clue, how to approach this ?

Thanks for any help!

[Gysbert_Wassenaar]

Try this: if(match([Type], 'Usable')=0 and Today()-[Activity Date]<365, 'Active', 'Inactive') as Active_Status

Make sure [Activity Date] is a date. If necessary use the Date#() function to turn a text string into a date value.

[#3]

Hi Gysbert,

I have already tried the expression if(match([Type], 'Usable')=0 and Today()-[Activity Date]<365, 'Active', 'Inactive') as Active_Status. But unfortunately, I did not get the correct result.

Thanks!

[Kushal_Chawda]

try this

if(match([Type], 'Usable')=0 and [Activity Date]>today()-365, 'Active', 'Inactive') as Active_Status

[sunny_talwar]

I guess can you share what are the expected results and what did you get? It is difficult to give any solution without knowing what is the current discrepancy.

[#6]

HI Kushal,

This does not work in QV.

Thank you for your help!

[settu_periasamy]

It would be good if you provide the sample data in excel or qvw.

[#8]

Hi,

Please find the data sample.

[Kushal_Chawda]

LET vDate = date(today()-365,'DD-MM-YYYY');

LOAD [Activity Date],

            [Type]

          if(match([Type], 'Usable') and [Activity Date]> date#('$(vDate)','DD-MM-YYYY'), 'Active', 'Inactive') as Active_Status

FROM table

[settu_periasamy]

Hi,

Like this?

LOAD *,if([Type]='Usable' and Today()-[Activity_Date]<365, 'Active', 'Inactive') as Active_Status,
  Today()-[Activity_Date] as Diff;
LOAD Activity_Date, 
     Type
FROM
[Data Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);