How to create a straight table with flagged fields

[cliff_clayman]

This is a little difficult to explain, but I will try my best to articulate what I am looking for.  I am looking to filter my results in a straight table to determine if a person has made a transaction within the last 3 months and the last 6 months.  I have a field called Period that needs to be evaluated to determine that.  I've attached a sample QVW file that will make a lot more sense once you look at it.  There is also a straight table that contains the data that is summed for all Periods.  So, the current Period value is P9.  I also created a variable that has that in it.  If a person has Charges for Periods 1-9, then I do not wish to see them in the table.  If they have a charge in P1 and then no further charges, I would like to see a flag in the 3 month and 6 month field in the table. If they have charges in P1-P5, then I just want to see a flag in the 3 month field.  Here is a screenshot that might make more sense...

Employee	Last Period	3 Month	6 Months	Total Charges
JOE SMITH	P1	x	x	$510.72
BOB JOHNSON	P2	x	x	$109.05
THOMAS DODD	P3	x	x	$645.88
ALEX RIVAS	P4	x		$713.64
CHRIS COLLINS	P5	x		$1,000.42

Let me know if there s any additional information I can provide.

[sinanozdemir]

Hi,

It is still not quite clear to me so if you can clarify the below examples, it would be great:

For example, A G MCCLANAGHAN:

so what is the expected output for this person?

and another example,

what is the expected output for A L RIVERA-LOZADA?

and lastly:

what is the expected output for A G OBERMEYER?

The screenshot/excerpt you provided in the question doesn't correspond to your data set so I just need some more clarification.

Thanks.

[cliff_clayman]

Sure...

Employee	Last Period	3 Months	6 Months	Total Charges
A G MCCLANAGHAN	P5	x		$681.42

The last two would not display as they are within 3 months.  A L RIVERA-LOZADA had a transaction in P7 and A G OBERMEYER had a transaction in P9, so they are within 3 months.  I am looking to display users that are not making recent transactions.

[sinanozdemir]

Ok.

What is P1 P2, or P9? Are they months?

I guess this is where I am really struggling to understand. If A G MCCLANAGHAN has P1-P5, why are we showing "3 months" marked, but not "6 months"?

Thanks

[cliff_clayman]

Yes, they are months.  So, if P9 is the current month and P5 was the last month a transaction occurred, then that person hasn't made a purchase within the last 3 months, but he has in the last 6 months.

[sinanozdemir]

Ok.

Thanks, let me take a stab at it.

[qliksus]

Hi ,

Maybe something like this

3Months : sum({<[PCard Full Name]=E({<Period={"=right(Period,1)>5"}>})>} TOTAL<[PCard Full Name]> [PCard Charge Amount] )  *

PurgeChar(Period=MaxString({<[PCard Full Name]=E({<Period={"=right(Period,1)>5"}>})>} total<[PCard Full Name]> Period),'-')

6 Months: sum({<[PCard Full Name]={"=Period=maxstring({<Period={'P1'}>}Period) "}>} TOTAL<[PCard Full Name]> [PCard Charge Amount] )

[cliff_clayman]

This is very close to what I was looking for.  However, I just wanted to see an 'x' in the 3 mon and 6 mon columns and a total $ column after that.

[cliff_clayman]

Any luck?

[sinanozdemir]

Hi Cliff,

Let me know if this is what you are looking for: