topic Year Help in Pivot Table in QlikView

Year Help in Pivot Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Year Help in Pivot Table

sunny_talwar — Tue, 24 Apr 2018 19:10:41 GMT

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

Re: Year Help in Pivot Table

Anonymous — Tue, 24 Apr 2018 19:16:15 GMT

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!

Re: Year Help in Pivot Table

sunny_talwar — Tue, 24 Apr 2018 19:18:42 GMT

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!

Re: Year Help in Pivot Table

sunny_talwar — Tue, 24 Apr 2018 19:22:44 GMT

Also, read about After and Before here

Missing Manual - Before() and After()

Re: Year Help in Pivot Table

Anonymous — Tue, 24 Apr 2018 19:26:31 GMT

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?

Re: Year Help in Pivot Table

sunny_talwar — Tue, 24 Apr 2018 19:27:52 GMT

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