Quarterly Ratio Bar graph

[aarohipatel]

Hello,

I am trying to create a Bar graph with ACCT_YEAR_QTR and Ratio 

Fields Incurred and Paid are ITD values. Premium is MTD value.

Ratio= QTR Incurred + QTR Paid / QTR Premium

Example 1:
For 2019 Q3
QTR Incurred= 2700 - 2400 = 300 (2019/03 incurred - 2018/12 incurred)
QTR Paid = 4300 - 4000 = 300 (2019/03 paid - 2018/12 paid)
QTR Premium = 500+600+700 = 1800 (2019/01 prem+2019/02 prem+ 2019/03 prem)

Ratio= 600/1800= 0.33

Attached is the qvw with sample data. 

Please advise. 

Thank you!

[sunny_talwar · Accepted solution]

New script

A:
LOAD Date#(ACCT_YEAR_MONTH, 'YYYY/MM') as ACCT_YEAR_MONTH,
Incurred,
Paid,
Premium;
LOAD * INLINE [
ACCT_YEAR_MONTH, Incurred, Paid, Premium
2017/12, 1200, 2800, 100
2018/01, 1300, 2900, 200
2018/02, 1400, 3000, 300
2018/03, 1500, 3100, 400
2018/04, 1600, 3200, 500
2018/05, 1700, 3300, 600
2018/06, 1800, 3400, 700
2018/07, 1900, 3500, 800
2018/08, 2000, 3600, 900
2018/09, 2100, 3700, 100
2018/10, 2200, 3800, 200
2018/11, 2300, 3900, 300
2018/12, 2400, 4000, 400
2019/01, 2500, 4100, 500
2019/02, 2600, 4200, 600
2019/03, 2700, 4300, 700
];

FinalA:
LOAD *,
Incurred - Alt(Peek('Incurred'), 0) as Incurred_NoCummulative,
Paid - Alt(Peek('Paid'), 0) as Paid_NoCummulative,
Premium - Alt(Peek('Premium'), 0) as Premium_NoCummulative,
Year(ACCT_YEAR_MONTH) & '/Q' & Ceil(Month(ACCT_YEAR_MONTH)/3) as ACCT_YEAR_QUARTER
Resident A;

DROP Table A;

expression

(Sum(Incurred_NoCummulative) + Sum(Paid_NoCummulative))/Sum(Premium)