topic Re: How do i write an average expression in QlikView

How do i write an average expression

Mon, 09 Nov 2015 14:19:42 GMT

Hi,

I am a relatively new user to Qlikview. I am trying to write an expression
within qlikview so that I can look at the average third party spend per year. I
will then duplicate this out and just change the years so that I can create a
chart with multiple years as they develop over the months.

I have been trying to amend an existing expression, but I am struggling:

rangesum( Above(sum(if([Loss Year]='2014',if(Section='' or Section=''or Section='',[TPD
Incurred]))), 0, rowno() ))

/

rangesum( Above(Count(if([Loss Year]='2014',if(Section='' or Section=''or Section='',[Closed
Count]))), 0, rowno() ))

I am unsure of the certain segments and I am using an expression where the
data was split by department level. I just want one overally view of the
incurred average per year.

I am unsure what the rowno means at the end too. Can anyone help?

Thanks,

Chris

Re: How do i write an average expression

swuehl — Mon, 09 Nov 2015 15:07:51 GMT

These two rangesum() functions essentially accumulate values.

Create two more expressions in the same chart.

=sum(if([Loss Year]='2014',if(Section='' or Section=''or Section='',[TPD Incurred])))

=Count(if([Loss Year]='2014',if(Section='' or Section=''or Section='',[Closed Count])))

Add another expression

=RowNo()

Above() will evaluate these expressions in the context of another dimension line, and above() with three arguments will do the same, but for more than one dimension line (in your sample, they will evaluate the expression for each dimension line from the top to the current line of the current column segment). Rangesum() will just aggregate each evalaute value.

Re: How do i write an average expression

Mon, 09 Nov 2015 15:12:29 GMT

Thanks for the reply.

I do not want the 'Section=' within the formula. All I want to pull is the average TPD incurred total for each loss year. The expression I published in my first query was one that someone had provided me with. It worked for what I previously needed, as I wanted to see the data by section.

Are you able to confirm how to search for just average TPD costs per loss year? I am unsure on the =rowno meaning. I know this was in the original expression but I did not write this so I am a bit confused as to what this offers.

Thanks,
Chris