Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Have you tried the AsofTable
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
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!
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)
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