topic Re: How to create a straight table with flagged fields in QlikView

How to create a straight table with flagged fields

cliff_clayman — Tue, 26 Jul 2016 20:45:59 GMT

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.

Re: How to create a straight table with flagged fields

sinanozdemir — Tue, 26 Jul 2016 21:31:32 GMT

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.

Re: How to create a straight table with flagged fields

cliff_clayman — Tue, 26 Jul 2016 21:35:53 GMT

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.

Re: How to create a straight table with flagged fields

sinanozdemir — Wed, 27 Jul 2016 17:26:07 GMT

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

Re: How to create a straight table with flagged fields

cliff_clayman — Wed, 27 Jul 2016 18:19:25 GMT

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.

Re: How to create a straight table with flagged fields

sinanozdemir — Wed, 27 Jul 2016 18:21:03 GMT

Ok.

Thanks, let me take a stab at it.

Re: How to create a straight table with flagged fields

qliksus — Thu, 28 Jul 2016 05:05:21 GMT

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

Re: How to create a straight table with flagged fields

cliff_clayman — Thu, 28 Jul 2016 13:54:45 GMT

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.

Re: How to create a straight table with flagged fields

cliff_clayman — Thu, 28 Jul 2016 20:40:06 GMT

Any luck?

Re: How to create a straight table with flagged fields

sinanozdemir — Fri, 29 Jul 2016 14:13:14 GMT

Hi Cliff,

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

Re: How to create a straight table with flagged fields

sinanozdemir — Fri, 29 Jul 2016 14:17:26 GMT

Hey Cliff,

Just a small edit:

Please see the new file.

Thanks

Re: How to create a straight table with flagged fields

cliff_clayman — Fri, 29 Jul 2016 14:19:57 GMT

Not quite. For example, A HERNANDEZ ALBARRAN shouldn't be in the result set. The current period is P9 and they made a purchase in P8, so that person should not show up in the list. Only those with no transactions in the last 3 months or greater should show up. Also, ALEX LOPEZ should have an 'X' in 6 months as the last transaction occurred in P1.

Re: How to create a straight table with flagged fields

cliff_clayman — Fri, 29 Jul 2016 14:22:05 GMT

ALEX LOPEZ is now correct. I would like to see an'X' in 3 months and 6 months if the last transaction occurred 6 months or more. However, AARON JOEL BITTERMAN should only have an 'X' in 3 months and anyone with a last period of P8 or P7 should not be displayed.

Re: How to create a straight table with flagged fields

sinanozdemir — Fri, 29 Jul 2016 14:34:03 GMT

Alright, take 2:

Hope this works

Re: How to create a straight table with flagged fields

cliff_clayman — Fri, 29 Jul 2016 14:41:48 GMT

That looks correct! The only thing I would change is using the Period for the calculation and not date.

Re: How to create a straight table with flagged fields

sinanozdemir — Fri, 29 Jul 2016 14:52:44 GMT

Cool.

I am glad it worked.