Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do i write an average expression

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

2 Replies
swuehl
MVP
MVP

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.

Not applicable
Author

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