Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Tapiwa0123
New Contributor

Monthly sum values in straight table

 Hi all,

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

SchemeMonthClaimsPremiums
Bronze22350
Silver34580
Gold33275
Bronze267100
Silver82390
Gold368120

 

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

SchemeJanuaryFebruaryMarch
Bronze0600
Silver0056.25
Gold00114.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..

2 Replies
pradosh_thakur
Honored Contributor II

Re: Monthly sum values in straight table

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

Learning never stops.
hirishv7
Honored Contributor

Re: Monthly sum values in straight table

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
“Aspire to Inspire before we Expire!”