Bar Chart for Rolling12 values

[Vimarsh]

I have a monthly dataset for different years (say 2017 and 2018) and I am trying t create a chart which would show the Rolling12 number at each month of 2018.

Month	Year	Amount
Jan	2017	400
Feb	2017	200
Mar	2017	300
Apr	2017	100
May	2017	500
Jun	2017	200
Jul	2017	500
Aug	2017	300
Sep	2017	300
Oct	2017	400
Nov	2017	100
Dec	2017	100
Jan	2018	500
Feb	2018	200
Mar	2018	200
Apr	2018	400
May	2018	400
Jun	2018	300
Jul	2018	100
Aug	2018	500
Sep	2018	500
Oct	2018	200
Nov	2018	100
Dec	2018	100

I want to create a graph for just 2018 months and show sum of amount for last 12 months against each month. How can I achieve this?

Something like below

Appreciate any help here!

Thank You!

[Vegar]

I did this script.

Data:
LOAD
AutoNumber(Month&Year) as  MonthYearSerial,
*
inline [
Month,Year,Amount
Jan,2017,400
Feb,2017,200
Mar,2017,300
Apr,2017,100
May,2017,500
Jun,2017,200
Jul,2017,500
Aug,2017,300
Sep,2017,300
Oct,2017,400
Nov,2017,100
Dec,2017,100
Jan,2018,500
Feb,2018,200
Mar,2018,200
Apr,2018,400
May,2018,400
Jun,2018,300
Jul,2018,100
Aug,2018,500
Sep,2018,500
Oct,2018,200
Nov,2018,100
Dec,2018,100];

 Its important that the periods are loaded in ascending order in order for the YearMonthSerial to be correct.

On this dataset I use the following expression:

=sum( aggr( rangesum(above(total sum({<[Month name],Month,Year>} Amount),0,12)),MonthYearSerial))

BR

- Vegar

[robert99]

Have you tried the AsofTable

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

[Vimarsh]

Thanks Vegar and Robert!

I should have stated few more things in my post. My bad for not putting it earlier.

The data I posted was just to get an idea. Actual data set is different and has few more columns which will be used as filter. Also, every month's data is not available and there may be gaps. I am okay if the graph is missing few months.

I am sharing a zip file containing sample of actual data and qvf app I have developed so far using AsofTable approach, Set Analysis and few dynamic variables. Just not able to create a bar chart for Rolling12Months where I need help.

Appreciate if you could share some feedback and ideas to create the bar chart!

[robert99]

Heres how to set up a c

Calendar andAsOfMonth

Script for Data Load

TRANSACTIONS:
LOAD
"Account Name",
Type,
Line,
Category,
Amount,
"Reporting Date" ,
"Reporting Date" as DateCanon
//FROM [lib://Desktop Qlik Folder/Sample Data.xlsx]
//(ooxml, embedded labels, table is Sheet1);

FROM [lib://Desktop Qlik Folder/Sample App & Data/Sample App & Data/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Script for Calendar with Sept Year end

Set vFM = 10 ; // year Oct to Sept First month of fiscal year

Calendar:
//level 5
Load
Dual(fYear-1 &'/'& fYear, fYear) as YearFin, // Dual fiscal year
Dual(Month, fMonth) as MonthFin, // Dual fiscal month for sorting
*
;
//level 4
Load
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*
;

Calendar: //calendar
//level 3
load
//DateCanon as FSRDate,
date(DateCanon) as DateCanon ,
Month(DateCanon) as Month,
Year(DateCanon) as Year,
Day (DateCanon) as Day,
weekday (DateCanon) as DayMonEtc,
weekend (DateCanon,0,-2) as WeekEnding ,
12*(Year(Today())-Year(DateCanon)) + Month(Today()) - Month(DateCanon) as MonthsAgo,
(Weekend(Today(),0,-2) - Weekend(DateCanon,0,-2))/7 as WeeksAgo ,
Mod(Month(Today())-$(vFM),12) - Mod(Month(DateCanon)-$(vFM),12) as MonthsAgoYTD ,

Today() - DateCanon as DaysAgo,
Week (DateCanon) AS WeekNum,
if(DateCanon < today()+90 ,MonthNAME (DateCanon) ) as MonthYear ,
MonthNAME (DateCanon) as MthYear ,

AddMonths( DateCanon,-12) as PrevYrDate , // set analysis >> MthYear etc = P(PrevYrDate). Leap yr 29/2 matched to 28/2

monthname(AddMonths( DateCanon,-12)) as PrevMthYr ,

// if(Month(DateCanon)= 'Feb' and day(DateCanon) = 29,MakeDate(year(DateCanon)-1, month(DateCanon), 28 ),
// MakeDate(year(DateCanon)-1, month(DateCanon), day(DateCanon) )) as PrevYrDate2 , // alternative

date(floor((Monthend(DateCanon)))) as MthEndDate ,
;
//level 2
Load
Date (MinDate+iterno()) as DateCanon
While iterno() <= MaxDate - MinDate ;
//level 1
Load Min(DateCanon)-1 as MinDate,
Max(DateCanon) as MaxDate
Resident TRANSACTIONS
;

Script for AsOfMonth

//---------AsOfMonth-------------------------------------------------------------------------

//---------AsOfMonth-------------------------------------------------------------------------

// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load
distinct MthYear
Resident [Calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load MthYear as AsOfMonth
Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of-Calendar]:
Load MthYear,
AsOfMonth,
AsOfMonth as AsOfMthYr,
Round((AsOfMonth-MthYear)*12/365.2425) as AsOfMth_MonthDiff,
Year(AsOfMonth)-Year(MthYear) as AsOfMth_YrDiff ,

Year(AsOfMonth)- year(MthYear) + If(num(Month(MthYear))>= 10 , -1, 0)
+ If(num(Month(AsOfMonth))>= 10 , 1, 0)
as AsOfMth_YrFinDiff ,

12*(Year(Today())- (Year(AsOfMonth)) + Month(Today()) - Month(AsOfMonth)) as AsofMth_MthsAgo,
Year(AsOfMonth)-Year(MthYear) as AsofMth_YrMthAgoDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= MthYear;

Drop Table tmpAsOfCalendar;

Measures

Dimension  >>>  AsOfMonth

For current Month

SUM({$<AsOfMth_MonthDiff={"<1"}>}Amount)

For rolling 12 months

SUM({$<AsOfMth_MonthDiff={"<12"}>}Amount)

[Vimarsh]

Thanks a lot Robert!!

I am going to make a few changes so that the number of months are automated based on user selection of the filter. I am going to use "GetFieldSelections" which is quite simple. Appreciate all your help!!

Best

Vimarsh Saxena