topic Monthly sum values in straight table in QlikView

Monthly sum values in straight table

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

Hi all,

I want to calculate the monthly loss ratios using data from the following table.

Scheme	Month	Claims	Premiums
Bronze	2	23	50
Silver	3	45	80
Gold	3	32	75
Bronze	2	67	100
Silver	8	23	90
Gold	3	68	120

I want to create a straight table that looks like the following loss ratios:

Scheme	January	February	March
Bronze	0	60	0
Silver	0	0	56.25
Gold	0	0	114.94

In the Dimensions, I have selected the 'Scheme' and in the Expression tab, I tried to calculate for month 2 using the following formula:

=if(Month=2, sum(Claims)/sum(Premiums)*100, 0)

This is producing wrong results. Please help..

Re: Monthly sum values in straight table

pradosh_thakur — Thu, 07 Feb 2019 05:18:17 GMT

load the table as

LOAD Scheme, 
     Month, 
     Claims, 
     Premiums,
     MONTH(date#(Month,'MM')) as month_name
FROM
table...........

Add a pivot table

dimensions1 - Schemes

dimension2 -Month name

expression

 sum(Claims)/sum(Premiums)*100

You may need to drag the column horizontally in Qlikview

Re: Monthly sum values in straight table

HirisH_V7 — Thu, 07 Feb 2019 07:00:49 GMT

Hi ,

you can go ahead with pradosh solution and work it out.

In case needed data a level changes means need to cross tab the data into that format in script level. By using cross table in File wizard while loading the data.

HTH,
HirisH