Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??
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.
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.
thank you so much, i think you've been made from the stuff of legends 😉
This does exactly what i intended to achieve.