2 Replies Latest reply: Jun 15, 2017 4:12 AM by Wendel van Rheenen RSS

    How do i calculate the correct data?

    Wendel van Rheenen

      Hi guy's,

       

      I'm looking for a way to calculate data and i'm a bit stuck in the process. I'm hoping you guys could help me out. Sales reps are entering forecast data of items they think they are going to sale (note a product group is a property of an item). So there for the situation is as follows: The data consists out of reference dates, were the last entry is the actual in time.


      With this in mind i'm looking to create a chart with dates (for example year-week, year-month or year-quarter) as dimension and the summed forecast quantity per site, per sales rep, per product group as calculated field.

       

      Example data:

       

      Items:

      Load * inline

      [itemNumber, productGroup

      1, wheel

      2, spoke,

      3, handlebar

      4, pedal

      ];

       

       

      ForcastData:

      Load * inline

      [itemNumber, site, salesRep, forecastDate, quantity

      1, london, jack, 01-01-2017, 5

      1, london, john, 01-05-2017, 15

      1, london, sandra, 01-09-2017, 2

      1, london, jack, 01-09-2017, 12

      1, amsterdam, bill, 01-09-2017, 2

      1, amsterdam, bob, 01-10-2017, 15

      2, berlin, mike, 02-03-2017,50

      ];

       

      So the forecast for wheels in January for the site londen should be 29 (jack- 12, john, 15, sandra 2)....

       

      i was looking into aggr and a set analysis or something a like. Do you guy's have any suggestions??

        • Re: How do i calculate the correct data?
          Nicole Smith

          If you add a month field in your script:

           

          Items:

          Load * inline

          [itemNumber, productGroup

          1, wheel

          2, spoke,

          3, handlebar

          4, pedal

          ];

           

           

          ForcastData:

          Load *, MonthName(forecastDate) as forecastMonth inline

          [itemNumber, site, salesRep, forecastDate, quantity

          1, london, jack, 01-01-2017, 5

          1, london, john, 01-05-2017, 15

          1, london, sandra, 01-09-2017, 2

          1, london, jack, 01-09-2017, 12

          1, amsterdam, bill, 01-09-2017, 2

          1, amsterdam, bob, 01-10-2017, 15

          2, berlin, mike, 02-03-2017,50

          ];

           

          You can use the following on a chart to get what you're looking for:

           

          Dimensions:

          forecastMonth

          site

           

          Expression:

          sum(aggr(FirstSortedValue(quantity, -forecastDate), forecastMonth, site, salesRep))

           

          I've attached an example file with this, and January for London = 29.