Hi, suppose I have a catalog of products. I would like to plot the catalog size (count of products) over time.
Refferring to the data below, I would like to build a line chart where x = SalesDate and y = count of products where ReleaseDate >= SalesDate.
Can you help me?
Thanks
Franco
Catalog:
LOAD * INLINE [
Publication_ID, Publication_Name, ReleaseDate
A, Pub A, 01/11/2014
B, Pub B, 01/04/2015
C, Pub C, 01/12/2015
D, Pub D, 01/12/2015
];
Sales:
LOAD * INLINE [
Serie_ID, Publication_ID, SalesDate, CopiesNum, CopiesRevenues
1, A, 01/11/2014, 10, 100
1, A, 01/03/2015, 30, 300
1, A, 01/05/2015, 10, 100
1, A, 01/07/2015, 30, 300
1, A, 01/11/2015, 10, 100
1, A, 01/02/2016, 20, 200
1, A, 01/03/2016, 10, 100
2, B, 01/04/2015, 10, 50
2, B, 01/02/2016, 40, 200
1, C, 01/12/2015, 20, 400
1, C, 01/02/2016, 40, 800
1, C, 01/03/2016, 10, 200
2, D, 01/12/2014, 30, 60
2, D, 01/12/2015, 20, 40
2, D, 01/01/2016, 10, 20
];
[Temp]:
LOAD
Max(SalesDate) as MaxValue,
Min(SalesDate) as MinValue
Resident Sales;
Let vEndDate=date(FieldValue('MaxValue',1),'DD/MM/YYYY');
Let vStartDate=date(FieldValue('MinValue',1),'DD/MM/YYYY');
Drop Table Temp;
[Calendar]:
LOAD
MonthName(SalesDate) As MonthName,
Year(SalesDate) as Year,
Autonumber(SalesDate) as MonthNum;
//*;
LOAD
Date(MonthStart('$(vStartDate)',iterno()-1)) as SalesDate
Autogenerate 1
While MonthStart('$(vStartDate)',iterno()-1) <= '$(vEndDate)';
