
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
YoY Growth
Hi Qlik Community,
I have been looking a lot on YoY Growth calculation posts, but none seem to work for me.
I have a dataset with 60 months of sales data.
The SCRIPT looks like this:
LOAD
Year([PERIOD]) as YEAR,
Month([PERIOD]) as MONTH,
MonthName([PERIOD]) as MONTHYEAR,
VALUE,
SALES_TYPE,
PRODUCT,
PERIOD
Value = Sales
Sales_TYPE = Units or Local Currency
PERIOD = Month (as MM/YYYY)
What I want to do is create a line chart with 60 months of sales points, displaying for each month the VALUE from last year (using dimensions MONTHYEAR & PRODUCT). For example, in OCT16, the value for OCT15 should be displayed, and in OCT15, the value for OCT14, etc etc.
Ultimately what I want to calculate is the Yearly Evolution Index for each month (market share from actual month, over Market Share from last year's month * 100).
Thanks a lot for your help,
Best,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you looked at The As-Of Table. This should fit your requirement perfectly

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
I created the As-Of Table but I am not able to calculate the YoY for each month anyway.
What I exactly need to do is for every month calculate (actual month / same month last year -1). For example for Feb16 = Feb16 / Feb15 -1
My script now looks like this:
DB_PM:
LOAD
AVG_PRICE,
VALUE,
SALES_TYPE,
PRODUCT,
PERIOD
FROM [lib://SENSE IMS/PM DATABASE\PM CSV HISTORICAL TEST\DB_PM_QLIKSENSE_HISTORICAL.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(PERIOD) as minDate,
max(PERIOD) as maxDate
Resident DB_PM;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS PERIOD,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct MonthYear
Resident [MasterCalendar] ;
// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load MonthYear as AsOfMonthYear
Resident tmpAsOfCalendar ;
// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load MonthYear,
AsOfMonthYear,
Round((AsOfMonthYear-MonthYear)*12/365.2425) as MonthYearDiff,
Year(AsOfMonthYear)-Year(MonthYear) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonthYear >= MonthYear;
Drop Table tmpAsOfCalendar;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to share what you have (with the AsOfDate calendar) and I might be able to suggest you a working solution?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
Sorry for the delay. I had to sample the data due to confidentialy issues.
Here you will be able to download the .qvf:
Qlik Sense App - Historic Sales - Google Drive
Thanks a lot for your help

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am unable to open that location, can you directly attach here?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
done.
thanks,
