Pulling above fields down caused by P()

[ttollin11]

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?

[sunny_talwar]

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

[ttollin11]

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.

[sunny_talwar]

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?

[ttollin11]

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?

[sunny_talwar]

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...

[ttollin11]

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.

[sunny_talwar]

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