Rolling 12 for attached excel in Bar chart in qlikview

[kartikaysingh]

Hi,

Below (big table) is what I have achieved as a Rolling 12 calculation (R12 Av) in excel. I want to replicate the same and calculate the R12 Av in Qlikview as a chart, bar would work.

What I want is,

For Filter YearMonth, whatever I select for e.g. if I select Jun-2017, I should get the bar value from Jul-2016 to Jun-2017 with R12Av value on each respective bar.

For reference -

R12Av for month Jun-2017 is calculated as

{

sum of R12 £ is for e.g. for Jun-2017 sum of below i.e. from Jul-2016 to Jun-2017

Jul-16	9,330,523.00
Aug-16	10,334,837.00
Sep-16	8,796,145.00
Oct-16	13,881,265.00
Nov-16	36,701,499.00
Dec-16	10,577,524.00
Jan-17	14,749,355.00
Feb-17	13,189,112.00
Mar-17	18,354,682.00
Apr-17	10,449,132.00
May-17	12,280,411.00
Jun-17	13,608,177.00

/ (divided by)

sum(R12# for Jun-2017)i.e. from Jul-2016 to Jun-2017

Jul-16	1333
Aug-16	1558
Sep-16	1270
Oct-16	1209
Nov-16	1288
Dec-16	1251
Jan-17	1392
Feb-17	1311
Mar-17	1648
Apr-17	1146
May-17	1536
Jun-17	1595

Same should happen for every YearMonth selection.

YearMonth	Incurred	Claim Count	R12
Jan-14	7,301,039.00	1325	5510
Feb-14	7,004,876.00	1553	4511
Mar-14	8,367,315.00	1977	4232
Apr-14	11,037,127.00	1865	5918
May-14	10,801,244.00	1708	6324
Jun-14	8,435,454.00	1620	5207
Jul-14	9,615,887.00	1667	5768
Aug-14	8,719,864.00	1238	7043
Sep-14	12,503,307.00	1447	8641
Oct-14	8,758,542.00	1477	5930
Nov-14	10,018,063.00	1403	7140	Month	R12 Av	R12 £	R12 #
Dec-14	10,257,436.00	1412	7264	Dec-14	6,036	112,820,154	18,692
Jan-15	6,024,823.00	1231	4894	Jan-15	5,998	111,543,938	18,598
Feb-15	6,915,577.00	1373	5037	Feb-15	6,051	111,454,639	18,418
Mar-15	9,642,458.00	1506	6403	Mar-15	6,281	112,729,782	17,947
Apr-15	9,645,093.00	1289	7483	Apr-15	6,409	111,337,748	17,371
May-15	13,581,436.00	1266	10728	May-15	6,741	114,117,940	16,929
Jun-15	8,749,704.00	1397	6263	Jun-15	6,850	114,432,190	16,706
Jul-15	9,733,178.00	1231	7907	Jul-15	7,041	114,549,481	16,270
Aug-15	7,672,727.00	1097	6994	Aug-15	7,037	113,502,344	16,129
Sep-15	6,012,754.00	1117	5383	Sep-15	6,773	107,011,791	15,799
Oct-15	9,116,868.00	1289	7073	Oct-15	6,878	107,370,117	15,611
Nov-15	10,045,735.00	1258	7985	Nov-15	6,944	107,397,789	15,466
Dec-15	8,925,378.00	1224	7292	Dec-15	6,942	106,065,731	15,278
Jan-16	7,645,712.00	1294	5909	Jan-16	7,020	107,686,620	15,341
Feb-16	8,521,357.00	1465	5817	Feb-16	7,082	109,292,400	15,433
Mar-16	11,092,997.00	1620	6848	Mar-16	7,123	110,742,939	15,547
Apr-16	9,917,940.00	1619	6126	Apr-16	6,992	111,015,786	15,877
May-16	11,860,046.00	1514	7834	May-16	6,778	109,294,396	16,125
Jun-16	10,283,455.00	1569	6554	Jun-16	6,801	110,828,147	16,297
Jul-16	9,330,523.00	1333	7000	Jul-16	6,734	110,425,492	16,399
Aug-16	10,334,837.00	1558	6633	Aug-16	6,707	113,087,602	16,860
Sep-16	8,796,145.00	1270	6926	Sep-16	6,811	115,870,993	17,013
Oct-16	13,881,265.00	1209	11482	Oct-16	7,124	120,635,390	16,933
Nov-16	36,701,499.00	1288	28495	Nov-16	8,683	147,291,154	16,963
Dec-16	10,577,524.00	1251	8455	Dec-16	8,767	148,943,300	16,990
Jan-17	14,749,355.00	1392	10596	Jan-17	9,132	156,046,943	17,088
Feb-17	13,189,112.00	1311	10060	Feb-17	9,491	160,714,698	16,934
Mar-17	18,354,682.00	1648	11138	Mar-17	9,903	167,976,383	16,962
Apr-17	10,449,132.00	1146	9118	Apr-17	10,219	168,507,575	16,489
May-17	12,280,411.00	1536	7995	May-17	10,231	168,927,940	16,511
Jun-17	13,608,177.00	1595	8532	Jun-17	10,416	172,252,662	16,537

[tussh077]

Hi Kartikay,

The solution can be implemented in two steps.

1. Add a new data table to your data model using below code.

2. Add MonthYear dimension and below expression to your line/ bar chart.

Hope this helps!

[arvind_patil]

Hi Kartikay,

Please find attachment it may help you.

Thanks,

Arvind Patil

[Kushal_Chawda]

check this

Rolling N months