Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Scalable month-to-date calculations in rows and within groups.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jun 25, 2014 7:52 AM
(1245 views)

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.

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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:

- In Graph Builder, select the grouping columns
- Right-click > Group By
- Select the columns to accumulate values for
- Right-click > Row > Cumulative Sum
- (see new temporary columns created)
- Select new temporary columns
- Right-click > Add to Data Table