Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted

Re: Pulling above fields down caused by P()

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

Highlighted
Contributor III
Contributor III

Re: Pulling above fields down caused by P()

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.

Highlighted

Re: Pulling above fields down caused by P()

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?

Highlighted
Contributor III
Contributor III

Re: Pulling above fields down caused by P()

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?

Highlighted

Re: Pulling above fields down caused by P()

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

Highlighted
Contributor III
Contributor III

Re: Pulling above fields down caused by P()

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.

Highlighted

Re: Pulling above fields down caused by P()

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