Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Commited Cost By Month between dates


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

Committed Cost.JPG

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

3 Replies
swuehl
MVP
MVP

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

MK_QSL
MVP
MVP

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

Not applicable
Author


Thanks Sewuehl that worked great.

Regards

Mark