Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

cliff_clayman
Contributor

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?

10 Replies

Re: Date functions within a straight table

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


talk is cheap, supply exceeds demand
cliff_clayman
Contributor

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.


talk is cheap, supply exceeds demand
cliff_clayman
Contributor

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


talk is cheap, supply exceeds demand
cliff_clayman
Contributor

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)


talk is cheap, supply exceeds demand
cliff_clayman
Contributor

Re: Date functions within a straight table

There is an error in that expression...

vishsaggi
Esteemed Contributor III

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)

Community Browser