0 Replies Latest reply: Nov 25, 2016 10:30 AM by Franco Giovannini RSS

    Plot catalog size over time

    Franco Giovannini

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