10 Replies Latest reply: Jul 26, 2016 2:02 AM by Gysbert Wassenaar

# Date functions within a straight table

I am looking to determine if a transaction date occurred in the last 3 or 6 months in a straight table.  The two fields are FullName and TransactionDate.  I need to find the last transaction date and compare it to today's date and determine if there has not been a transaction within the last 3 months and the last 6 months or greater.  How would I go about doing this?

• ###### Re: Date functions within a straight table

Perhaps like this: If([ Today() - TransactionDate > 90 and Today() - TransactionDate < 180 , 'Yes', 'No')

• ###### Re: Date functions within a straight table

That gives me the raw data I need, but how do I limit it to just see those names that have not had a transaction within the last 3 and 6 months?

• ###### Re: Date functions within a straight table

Please post a small qlikview document with example data.

• ###### Re: Date functions within a straight table

Here is some sample data with a straight table. !

• ###### Re: Date functions within a straight table

Try

If( Today() -only( {<FullName=E({<TransactionDate={"=TransactionDate>Today()-180 and TransactionDate<Today()-90"}>}FullName)>}  [TransactionDate] ) > 90 and Today() -only( {<FullName=E({<TransactionDate={"=TransactionDate>Today()-180 and TransactionDate<Today()-90"}>}FullName)>}  [TransactionDate] )< 180 , 'No', 'Yes')

• ###### Re: Date functions within a straight table

I only want to see the last transaction date if there are multiple dates for a name.  How can I do that?

• ###### Re: Date functions within a straight table

Try max(<FullName=E({<TransactionDate={"=TransactionDate>Today()-180 and TransactionDate<Today()-90"}>}FullName)>} TransactionDate)

• ###### Re: Date functions within a straight table

There is an error in that expression...

• ###### Re: Date functions within a straight table

Is that flower bracket that is missing Gysbert?

Add Flower bracket highlighed in red and try Cliff...

max({<FullName = E({< TransactionDate={"=TransactionDate>Today()-180 and TransactionDate<Today()-90"}>}FullName)>} TransactionDate)

• ###### Re: Date functions within a straight table

Yes, well spotted. Thanks.