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

ibc

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:

		Version1	Version1	Version1	Version1	Version2	Version2	Version2	Version2
Family	Product	Week1	Week2	Week3	Week4	Week1	Week2	Week3	Week4
A	X1	10	10	12	11	10	10	11	13
A	X3	13	12	13	12	14	15	14	15
B	X2	12	13	12	13	11	13	11	13
B	X4	11	9	10	9	8	9	8	9
B	X5	10	10	11	11	11	11	10	10

Actual Sales:

Family	Product	Week1	Week2	Week3	Week4
A	X1	10	12	11	12
A	X3	14	15	14	15
B	X2	12	13	12	13
B	X4	8	9	8	9
B	X5	10	10	11	11

Thanks.

XanGregg

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