topic Re: Inserting a calculated %ge line into a straight table in QlikView

Inserting a calculated %ge line into a straight table

paul_ripley — Mon, 30 Apr 2018 10:57:56 GMT

Hello

I have a table below and the layout is based on a table I load from excel.

I would like to add in a line below Gross Investment Return for '% of opening portfolio'

I could add in a line in excel for this, but this line will be a calculation rather than coming from the database.

Do you know how I can add in a row like this?Many thanks

Paul

Re: Inserting a calculated %ge line into a straight table

sunny_talwar — Mon, 30 Apr 2018 11:02:08 GMT

May be look here

How to Create a Profit and Loss Statement in QlikView

Re: Inserting a calculated %ge line into a straight table

paul_ripley — Mon, 30 Apr 2018 13:03:19 GMT

Hi Sunny

That did help. I amdended my excel sheet and put in a line '% of opening portfolio'

And I amended the YTD column to say

=if( pl_line_ifrs = '% of opening portfolio', '1', sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}>} $(vIS_Investment) )/1000000)

Just to test and it did bring back a '1'.

But as soon as I put any simple calculation in here it then doesn't bring back this line eg

=if( pl_line_ifrs = '% of opening portfolio', sum(IS_Investment_GBP) , sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}>} $(vIS_Investment) )/1000000)

Do you have any idea why this might be?

Re: Inserting a calculated %ge line into a straight table

sunny_talwar — Mon, 30 Apr 2018 13:06:02 GMT

Try adding TOTAL

=If(pl_line_ifrs = '% of opening portfolio', Sum(TOTAL IS_Investment_GBP), Sum({$<period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}>} $(vIS_Investment) )/1000000)

Re: Inserting a calculated %ge line into a straight table

paul_ripley — Mon, 30 Apr 2018 14:11:27 GMT

Thanks very much for you your help. It has helped me to make progress on my report

Paul