# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Creator

## Year Help in Pivot Table

Hello,

I am looking to create a pivot table that looks like:

However I need the following 2017 and 2016 filled in for the Persistency row.

The persistency equation should take the current year exposures / by the previous years exposures.  I tried using the above and below function and I cannot seem to get this to act properly. eg. this should be 2017 Renew Exposures / 2016 Exposures in the first column and then 2016 Renew Exposures / 2015 Exposures in the second column.  The third column should be blank.

Here is the equation I tried:

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])
/
aggr(Above(sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS])), [ROLLED AY])

Just Looking for some help to get this to behave properly.

Thanks,

Justin

1 Solution

Accepted Solutions
MVP

You have multiple options

1) Try this

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])
/
Aggr(Below(Sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS])), [ROLLED AY])

2) Try with After function without Aggr()

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])

/

After(Sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS]))

6 Replies
MVP

You have multiple options

1) Try this

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])
/
Aggr(Below(Sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS])), [ROLLED AY])

2) Try with After function without Aggr()

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])

/

After(Sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS]))

Creator
Author

Hi Sunny,

Perfect thank you for the help!  Option 2 worked perfectly.  When I tried option 1 it filled in 2017 and 2015.  Curious why that happened.  Either way, I really appreciate the help!

MVP

May be because of sorting of year field in the script... do you have QV12 or above? If you do, you can try this

Sum({<[ROLLED AY] = ,  SEGMENT-={' '}, [ANNUALIZED NEW/RENEWAL DESCRIPTION]= {'RENEW'}>}[BI WRITTEN EXPOSURE YEARS])

/

Aggr(Below(Sum({<[ROLLED AY]=,SEGMENT-={' '}>} [BI WRITTEN EXPOSURE YEARS])), ([ROLLED AY], (NUMERIC)))

This was the new functionality which was brought into QlikView and Qlik Sense. Read more about this here :The sortable Aggr function is finally here!

MVP