Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Contributor III

Pulling above fields down caused by P()

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|2832711/1/2017201711\$64.959/1/2017IB\$64.95\$0.00\$64.95
201711|5054611/1/2017201711\$335.909/1/2017NIB\$335.90\$0.00\$335.90
201711|5669511/1/2017201711\$10140.639/1/2017NIB\$10140.63\$0.00\$10140.63
201711|6313611/1/2017201711\$211073.389/1/2017IB\$211073.38\$0.00\$211073.38
201711|8625711/1/2017201711\$12057.659/1/2017NIB\$12057.65\$0.00\$12057.65
201711|14194411/1/2017201711\$994389.749/1/2017IB\$994389.74\$0.00\$994389.74
201711|25475611/1/2017201711\$167483.779/1/2017NIB(\$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?

7 Replies
MVP

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

Contributor III
Author

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|2832711/1/2017201711\$64.959/1/2017IB\$64.95\$105333.08\$64.95
201711|5054611/1/2017201711\$335.909/1/2017NIB\$335.90\$0.00\$335.90
201711|5669511/1/2017201711\$10140.639/1/2017NIB\$10140.63\$0.00\$10140.63
201711|6313611/1/2017201711\$211073.389/1/2017IB\$211073.38\$0.00\$211073.38
201711|8625711/1/2017201711\$12057.659/1/2017NIB\$12057.65\$0.00\$12057.65
201711|14194411/1/2017201711\$994389.749/1/2017IB\$994389.74\$0.00\$994389.74
201711|25475611/1/2017201711\$167483.779/1/2017NIB(\$115972.84)\$0.00(\$115972.84)

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

MVP

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?

Contributor III
Author

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?

MVP

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

Contributor III
Author

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.

MVP

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

Community Browser