Subscribe Bookmark RSS Feed

Scalable month-to-date calculations in rows and within groups.

ibc

Community Member

Joined:

Jun 25, 2014

Hello,

I have two data sets which have weekly forecasts and sales for a number of products. Each products belong to a few number of product families. I want to work this out in JMP so that I can start adopting its functionalities instead of Excel.

I am trying to inspect month-to-date deltas within a version for a month; e.g., delta for a target Week 2 would be the absolute difference between the total forecasts for Week1&Week2 minus the total sales for Week1&Week2. For instance, for forecast version 1, Week2 delta will be ABS(22-20)=2, Week4 delta would be ABS((10+10+12+11)-(10+12+11+12))=2.

How should I use JMP to be able to do this both at the product and at the product family level aggregation in a scalable manner so that I could replicate this at larger versions? My data looks like this on separate sheets for forecasts and actuals.

Forecasts:

Version1Version1Version1Version1Version2Version2Version2Version2
FamilyProductWeek1Week2Week3Week4Week1Week2Week3Week4
AX11010121110101113
AX31312131214151415
BX21213121311131113
BX41191098989
BX51010111111111010

Actual Sales:

FamilyProductWeek1Week2Week3Week4
AX110121112
AX314151415
BX212131213
BX48989
BX510101111

Thanks.

1 REPLY
XanGregg

Staff

Joined:

Jun 23, 2011

For maximum scalability you probably want to make the week number into a single column with one week per row. Then each week is a row and has forecasts and actual values in separate columns. Data columns could be Family, Product, Week, Forecast V1, Forecast V2, Actual. Then add formula columns that are computed: Cumulative Forecast V1, Cumulative Forecast V2, Cumulative Actual, Delta V1 and Delta V2. The last two are simple subtractions from the cumulative columns.

The Cumulative formulas will be more complicated. You might find examples in this forum, I use the Cumulative Sum feature of Transform Columns in Graph Builder:

  1. In Graph Builder, select the grouping columns
  2. Right-click > Group By
  3. Select the columns to accumulate values for
  4. Right-click > Row > Cumulative Sum
  5. (see new temporary columns created)
  6. Select new temporary columns
  7. Right-click > Add to Data Table