topic Re: Pivottables, dimensions, formulae, graphs in QlikView

Pivottables, dimensions, formulae, graphs

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

Hi All,

I have data which I calculated in the following table, each row is a formula sum(Claims)/sum(GEP).

Period	Loss Ratio
Month 1	54.5
Month 2	55.7
Month 3	57.8
Month 4	57.9
Month 5	59.8
Month 6	58.7
Month 7	60.7

I would like to have calculated using the dimension Underwriting Year:

Period	Loss Ratio UWY 2014	Loss Ratio UWY 2015
Month 1	55.5	22.8
Month 2	56.7	23.7
Month 3	55.8	26.7
Month 4	58.9	30.5
Month 5	60.8	29.6
Month 6	59.7	55.5
Month 7	66.7	56.7

However, when I add the dimension, I calculates not the individual loss ratios per UWY. If I created a table for each UWY, I would use the formula sum({<UWY='2014'>}Claims)/sum({<UWY='2014'>}GEP).

But I would like to be able to creat a graph like this:

How would I do that? As I am not an English speaker, I am unsure what to look for in the forum.

Many thanks for your help and sorry, if this question has already been asked.

Best regards,

Jan

Re: Pivottables, dimensions, formulae, graphs

Anil_Babu_Samineni — Thu, 31 Aug 2017 08:18:37 GMT

May be create like below

Dimension - Period

Label - ='Loss Ratio UWY' & Max(Year)-1

Expression 1 - sum({<UWY={'2014'}>}Claims)/sum({<UWY={'2014'}>}GEP)

Or This is for Dynamic

sum({<UWY={'$(=Max(Year)-1)'}>}Claims)/sum({<UWY={'$(=Max(Year)-1)'}>}GEP)

Label - ='Loss Ratio UWY' & Max(Year)

Expression 2 - sum({<UWY={'2014'}>}Claims)/sum({<UWY={'2014'}>}GEP)

Or This is for Dynamic

sum({<UWY={'$(=Max(Year))'}>}Claims)/sum({<UWY={'$(=Max(Year))'}>}GEP)

Re: Pivottables, dimensions, formulae, graphs

sergio0592 — Thu, 31 Aug 2017 08:57:40 GMT

If Year is a dimension, you can simply create a pivot chart with Year as column. And after trasform your pivot in graph.