14 Replies Latest reply: Apr 11, 2016 7:19 PM by Marco Wedel

# To compare a date field less than or grater than12 months

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!

• ###### Re: To compare a date field less than or grater than12 months

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.

• ###### Re: To compare a date field less than or grater than12 months

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!

• ###### Re: To compare a date field less than or grater than12 months

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.

• ###### Re: To compare a date field less than or grater than12 months

try this

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

• ###### Re: To compare a date field less than or grater than12 months

HI Kushal,

This does not work in QV.

• ###### Re: To compare a date field less than or grater than12 months

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

• ###### Re: To compare a date field less than or grater than12 months

Hi,

• ###### Re: To compare a date field less than or grater than12 months

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

[Type]

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

FROM table

• ###### Re: To compare a date field less than or grater than12 months

Hi,

Like this?

```LOAD *,if([Type]='Usable' and Today()-[Activity_Date]<365, 'Active', 'Inactive') as Active_Status,
Today()-[Activity_Date] as Diff;
Type
FROM
[Data Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
```
• ###### Re: To compare a date field less than or grater than12 months

Hi ,

Doing this way gives me duplicate results  as show in the attachment  :-

• ###### Re: To compare a date field less than or grater than12 months

Those all seem to be future dates, may you need this as well:

If([Type]='Usable' and Today()-[Activity_Date] < 365 and Today()-[Activity_Date] >= 0, 'Active', 'Inactive') as Active_Status,

Today()-[Activity_Date] as Diff;

Type

FROM

[Data Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

UPDATE: Used Settu's QVW code to make modifications

• ###### Re: To compare a date field less than or grater than12 months

yes, I too agree with you but this still does not help either

• ###### Re: To compare a date field less than or grater than12 months

Can you share the script you are using right now?

• ###### Re: To compare a date field less than or grater than12 months

Hi,

one solution could be:

```table1:
Type,
If(Type='Usable' and Activity_Date>AddYears(Today(),-1), 'Active', 'Inactive') as Active_Status