topic Pulling above fields down caused by P() in QlikView

Pulling above fields down caused by P()

ttollin11 — Wed, 18 Jul 2018 17:52:07 GMT

I am trying to get a Quarter over Quarter metric by pulling an amount from a previous date in a set analysis. That function is:

=sum({<[Record Date] = P([Previous Quarter End]), [Deposit Month], [Deposit Quarter], [Deposit Year]>} [Current Balance Amount])

where [Previous Quarter End] is

MonthStart(QuarterStart(TempDate) - 1) As [Previous Quarter End]

When I do this, the following table is an example of the result:

%DepositFact_PK	=[Record Date]	=RecordYYYYMM	=[Current Balance Amount]	=[Previous Quarter End]	[Interest Bearing Account]	Net Trans Amt	Previous Qtr Bal	Q o Q Change
	11/1/2017	201711		9/1/2017			$2528811.11
201709\|28327							$125.75
201709\|50546							$400.50
201709\|56695							$26108.53
201709\|63136							$210941.88
201709\|86257							$132542.57
201709\|141944							$2053358.80
201709\|254756							$105333.08
201711\|28327	11/1/2017	201711	$64.95	9/1/2017	IB	$64.95	$0.00	$64.95
201711\|50546	11/1/2017	201711	$335.90	9/1/2017	NIB	$335.90	$0.00	$335.90
201711\|56695	11/1/2017	201711	$10140.63	9/1/2017	NIB	$10140.63	$0.00	$10140.63
201711\|63136	11/1/2017	201711	$211073.38	9/1/2017	IB	$211073.38	$0.00	$211073.38
201711\|86257	11/1/2017	201711	$12057.65	9/1/2017	NIB	$12057.65	$0.00	$12057.65
201711\|141944	11/1/2017	201711	$994389.74	9/1/2017	IB	$994389.74	$0.00	$994389.74
201711\|254756	11/1/2017	201711	$167483.77	9/1/2017	NIB	($115972.84)	$0.00	($115972.84)

I need to get the values above to be in the records of the associated PKs so that the follow-on field, Q o Q, can calculate. What am I doing wrong?

Re: Pulling above fields down caused by P()

sunny_talwar — Wed, 18 Jul 2018 18:06:16 GMT

You can either use Above() function to do this Missing Manual - Above() and Below() or you can use The As-Of Table

Re: Pulling above fields down caused by P()

ttollin11 — Wed, 18 Jul 2018 19:28:43 GMT

Using Above() seems to only bring down the value from PK 254756 to PK 28327...

%DepositFact_PK	=[Record Date]	=RecordYYYYMM	=[Current Balance Amount]	=[Previous Quarter End]	[Interest Bearing Account]	Net Trans Amt	Previous Qtr Bal	Q o Q Change
	11/1/2017	201711		9/1/2017
201709\|50546							$125.75
201709\|56695							$400.50
201709\|63136							$26108.53
201709\|86257							$210941.88
201709\|141944							$132542.57
201709\|254756							$2053358.80
201711\|28327	11/1/2017	201711	$64.95	9/1/2017	IB	$64.95	$105333.08	$64.95
201711\|50546	11/1/2017	201711	$335.90	9/1/2017	NIB	$335.90	$0.00	$335.90
201711\|56695	11/1/2017	201711	$10140.63	9/1/2017	NIB	$10140.63	$0.00	$10140.63
201711\|63136	11/1/2017	201711	$211073.38	9/1/2017	IB	$211073.38	$0.00	$211073.38
201711\|86257	11/1/2017	201711	$12057.65	9/1/2017	NIB	$12057.65	$0.00	$12057.65
201711\|141944	11/1/2017	201711	$994389.74	9/1/2017	IB	$994389.74	$0.00	$994389.74
201711\|254756	11/1/2017	201711	$167483.77	9/1/2017	NIB	($115972.84)	$0.00	($115972.84)

I am not sure I understand how to apply the As-Of in this scenario.

Re: Pulling above fields down caused by P()

sunny_talwar — Wed, 18 Jul 2018 19:34:43 GMT

You have 6 ids in there... so you need 6 in the above... but this will change based on the id

=Above(sum({<[Record Date] = P([Previous Quarter End]), [Deposit Month], [Deposit Quarter], [Deposit Year]>} [Current Balance Amount]), 6)

Now you can def. make the 6 dynamic based on the count of id, but I still think that As Of Table is the way to go.

In order to help you with As Of Table, would you be able to share a sample where we can demonstrate how you can use it in your scenario?

Re: Pulling above fields down caused by P()

ttollin11 — Thu, 19 Jul 2018 13:00:45 GMT

Thanks Sunny. I gave the dynamic argument for above() a try and the load time for the object is ridiculous when there are fewer filters, so I think that's out. What information do you need in the sample?

Re: Pulling above fields down caused by P()

sunny_talwar — Thu, 19 Jul 2018 13:03:30 GMT

I guess raw data for the fields you are using to create the chart above.... The more you can provide, the better it will be...

Re: Pulling above fields down caused by P()

ttollin11 — Thu, 19 Jul 2018 14:02:24 GMT

I've tried to recreate the relevant data from the table above in the attached sample on the original post. Let me know if this isn't sufficient.

Re: Pulling above fields down caused by P()

sunny_talwar — Thu, 19 Jul 2018 14:22:10 GMT

This may not be perfect, but you will get an idea... check attached