topic Re: Commited Cost By Month between dates in QlikView

Commited Cost By Month between dates

Fri, 03 Jul 2015 10:24:05 GMT

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

Re: Commited Cost By Month between dates

swuehl — Fri, 03 Jul 2015 10:37:33 GMT

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

Re: Commited Cost By Month between dates

MK_QSL — Fri, 03 Jul 2015 10:47:52 GMT

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])

Re: Commited Cost By Month between dates

Sat, 04 Jul 2015 10:05:57 GMT

Thanks Sewuehl that worked great.

Regards

Mark