Year Help in Pivot Table

[Anonymous]

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

[sunny_talwar · Accepted solution]

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]))

[Anonymous]

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!

[sunny_talwar]

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!

[sunny_talwar]

Also, read about After and Before here

Missing Manual - Before() and After()

[Anonymous]

Great thank you again.  I do not have QV 12 yet.  I will try to look at the sorting for the Rolled AY.  If it makes sense should I set up a sort in script?

[sunny_talwar]

Yes, if you can sort Rolled AY in ascending order in the script... your Aggr() with Before should work without any issues