Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Vimarsh
New Contributor II

Bar Chart for Rolling12 values

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.

MonthYearAmount
Jan2017400
Feb2017200
Mar2017300
Apr2017100
May2017500
Jun2017200
Jul2017500
Aug2017300
Sep2017300
Oct2017400
Nov2017100
Dec2017100
Jan2018500
Feb2018200
Mar2018200
Apr2018400
May2018400
Jun2018300
Jul2018100
Aug2018500
Sep2018500
Oct2018200
Nov2018100
Dec2018100

 

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

Img3.JPG

Appreciate any help here!

Thank You!

1 Solution

Accepted Solutions
robert99
Valued Contributor II

Re: Bar Chart for Rolling12 values

 
6 Replies
Partner
Partner

Re: Bar Chart for Rolling12 values

image.png

 

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
robert99
Valued Contributor II

Re: Bar Chart for Rolling12 values

Vimarsh
New Contributor II

Re: Bar Chart for Rolling12 values

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
Valued Contributor II

Re: Bar Chart for Rolling12 values

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)

 

 

 

 

robert99
Valued Contributor II

Re: Bar Chart for Rolling12 values

 
Vimarsh
New Contributor II

Re: Bar Chart for Rolling12 values

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