topic Re: Date functions within a straight table in QlikView

Date functions within a straight table

cliff_clayman — Mon, 25 Jul 2016 17:14:11 GMT

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

Gysbert_Wassenaar — Mon, 25 Jul 2016 17:36:40 GMT

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

Re: Date functions within a straight table

cliff_clayman — Mon, 25 Jul 2016 17:48:05 GMT

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

Gysbert_Wassenaar — Mon, 25 Jul 2016 17:51:15 GMT

Please post a small qlikview document with example data.

Re: Date functions within a straight table

cliff_clayman — Mon, 25 Jul 2016 18:07:14 GMT

Here is some sample data with a straight table. !

Re: Date functions within a straight table

Gysbert_Wassenaar — Mon, 25 Jul 2016 18:15:57 GMT

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

cliff_clayman — Mon, 25 Jul 2016 18:17:55 GMT

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

Gysbert_Wassenaar — Mon, 25 Jul 2016 18:22:53 GMT

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

Re: Date functions within a straight table

cliff_clayman — Mon, 25 Jul 2016 18:31:08 GMT

There is an error in that expression...

Re: Date functions within a straight table

vishsaggi — Mon, 25 Jul 2016 18:38:30 GMT

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

Gysbert_Wassenaar — Tue, 26 Jul 2016 06:02:26 GMT

Yes, well spotted. Thanks.