Approximate costings

[james_hanify]

Hi all,

So I have a scenario where we don't always have our costings on the system for a product we have sold.

My idea was that maybe I could use a costings table

Basic premise being

ItemCode	TransactionDate	CostPerUnit
G000AAA	01/04/2017	0.05
G000AAA	06/04/2017	0.07
G000BBB	01/08/2017	0.65

Whilst it may not be absolutely the best way of doing it, and I would need to put some logic in it to not always do this.

I was half tempted to create a kind of rota thing say that 03/04/17 for G000AAA would be 0.06 where it would look at the differences in price per day and could look up via that or failing that, the nearest price.

Can anyone please give me an insight into doing what I am trying to do, if its even possible.

James

[adamdavi3s]

Hi James,

Please find attached an example of this, I think I've understood your requirements but obviously the example is a simple one.

Just ask if you have any questions about it at all.

Kind regards

Adam

[james_hanify]

Hi Adam,

Thanks for this,

But if i'm right, yours kind of creates a value based on the average? Rather than the average based on the order date overall?

The idea is to get an average based on the transaction date.

So e.g. Product A has price 30p on 1st, 35p on 5th, so if invoiced on the 3rd it would be something like 32.5p

James.

[adamdavi3s]

Hi James,

sorry trying to remember what I did for this one

So, my main aim here was to show how you could use a static price table in the absence of any prior pricing being available. - Does this bit work how you anticipated?

Regarding the average price, in this case yes Qlik is just calculating the average based on any selections, so in the raw form then yes the average for all time. If you select a date or a range then it will show the average for that range.

So it depends what your scenario is, if it is a case that you want the average to update every time new data is loaded then this will be working correctly.

However I am guessing your scenario is something like the attached in that you want the invoice price on a set date to be calculated based on all previous orders to that date? I haven't quite got time to figure the logic but I bet you stalwar1‌ will be able to knock it out in a second

[sunny_talwar]

I will check this out, but from OPs description it seems that he might need a weighted average based on dates here... I will check this out once I reach office

[sunny_talwar · Accepted solution]

I could be totally wrong, but is this what you want?

Where the input is this

Table:

LOAD * INLINE [

    ItemCode, TransactionDate, CostPerUnit

    G000AAA, 01/04/2017, 0.05

    G000AAA, 06/04/2017, 0.07

    G000AAA, 15/04/2017, 0.12

    G000AAA, 02/05/2017, 0.26

    G000BBB, 01/08/2017, 0.65

    G000BBB, 05/08/2017, 0.68

    G000BBB, 15/08/2017, 0.71

];

Using the above data as a starting point, I created a CostPerUnit for each TransactionDate using this script

FinalTable:

NoConcatenate

LOAD ItemCode,

  Date(TransactionDate + IterNo() - 1) as TransactionDate,

  Round(CostPerUnit + (IterNo()-1)*IncrementalCost, 0.0001) as CostPerUnit

While TransactionDate + IterNo() - 1 < NextDate;

LOAD *,

  (NextCost-CostPerUnit)/(NextDate-TransactionDate-1) as IncrementalCost;

LOAD *,

  If(ItemCode = Previous(ItemCode), Previous(TransactionDate)) as NextDate,

  If(ItemCode = Previous(ItemCode), Previous(CostPerUnit)) as NextCost

Resident Table

Order By ItemCode, TransactionDate desc;

DROP Table Table;

[james_hanify]

That sounds spot on, thanks a lot