Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Thought this was quite simple but haven't had much luck, wanted to show the the total committed cost per month for all outstanding Purchase orders.
So for each Month the Value of the Purchase Order is included if its between the Order Date and the Delivery date.
As Shown in the simple example below, all i need is a way of calculating the Total in a Chart.
Thanks
Mark
Maybe like this:
Set DateFormat = 'DD.MM.YYYY';
Deliveries:
LOAD * INLINE [
OrderNo, OrderDate, DeliveryDate, OrderValue
1, 03.05.2015,15.09.2015,100
2, 02.02.2015,03.08.2015,200
3, 23.01.2015,07.07.2015,50
4, 23.04.2015,20.09.2015,150
];
LOAD OrderNo,
MonthName(OrderDate, iterno()-1) as MonthName,
OrderValue as Value
RESIDENT Deliveries
WHILE MonthName(OrderDate,iterno()-1) <= MonthName(DeliveryDate);
Then create your pivot chart with dimensions OrderNo and MonthName and expression =Sum(Value)
edit: Set Date format to used format
Maybe like this:
Set DateFormat = 'DD.MM.YYYY';
Deliveries:
LOAD * INLINE [
OrderNo, OrderDate, DeliveryDate, OrderValue
1, 03.05.2015,15.09.2015,100
2, 02.02.2015,03.08.2015,200
3, 23.01.2015,07.07.2015,50
4, 23.04.2015,20.09.2015,150
];
LOAD OrderNo,
MonthName(OrderDate, iterno()-1) as MonthName,
OrderValue as Value
RESIDENT Deliveries
WHILE MonthName(OrderDate,iterno()-1) <= MonthName(DeliveryDate);
Then create your pivot chart with dimensions OrderNo and MonthName and expression =Sum(Value)
edit: Set Date format to used format
Temp_Order:
Load
[Order No.],
Date(Date#([Order Date],'DD-MMM-YY')) as [Order Date],
Date(Date#([Delivery Date], 'DD-MMM-YY')) as [Delivery Date],
[Order Value]
Inline
[
Order No., Order Date, Delivery Date, Order Value
1, 03-May-15, 15-Sep-15, 100
2, 02-Feb-15, 03-Aug-15, 200
3, 23-Jan-15, 07-Jul-15, 50
4, 23-Apr-15, 20-Sep-15, 150
];
Order:
Load
[Order No.],
[Order Date],
Date(MonthStart([Order Date],IterNo()-1),'MMM-YY') as MonthYear,
[Delivery Date],
[Order Value]
Resident Temp_Order
While MonthStart([Order Date],IterNo()-1) <= MonthStart([Delivery Date]);
Drop Table Temp_Order;
Now Create a Pivot Table
Dimension
[Order No.]
MonthYear
Expression
SUM([Order Value])
Thanks Sewuehl that worked great.
Regards
Mark