Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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!



14 Replies

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.


talk is cheap, supply exceeds demand
Not applicable

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

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

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.

Not applicable

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

HI Kushal,

This does not work in QV.

Thank you for your help!

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.

Not applicable

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

Hi,

Please find the data sample.

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

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

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;

LOAD Activity_Date,

     Type

FROM

[Data Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Community Browser