topic Re: Bar Chart for Rolling12 values in App Development

Bar Chart for Rolling12 values

Vimarsh — Tue, 13 Aug 2019 21:29:45 GMT

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!

Re: Bar Chart for Rolling12 values

Vegar — Wed, 14 Aug 2019 08:25:25 GMT

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))

- Vegar

Re: Bar Chart for Rolling12 values

robert99 — Wed, 14 Aug 2019 10:06:43 GMT

Have you tried the AsofTable

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

Re: Bar Chart for Rolling12 values

Vimarsh — Thu, 15 Aug 2019 02:10:40 GMT

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!

Re: Bar Chart for Rolling12 values

robert99 — Thu, 15 Aug 2019 06:27:52 GMT

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-------------------------------------------------------------------------

// ======== 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)

Re: Bar Chart for Rolling12 values

robert99 — Thu, 15 Aug 2019 06:29:51 GMT

Re: Bar Chart for Rolling12 values

Vimarsh — Tue, 03 Sep 2019 00:46:48 GMT

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