topic Re: Days of Supply logic in QlikView

Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 14:32:43 GMT

Hello experts,

I am facing a challenge and I hope you can help. I need to build a days of supply logic that look into the future forecast. I found a similar post, however it gave a partial solution. Find post below.

Days of Supply Problem

The main difference on my logic is that we usually keep around 6 days of supply, so we need to use the Total Stock (Total Receipts + Stock on Hand) and check how many days based on forecast we will be able to cover. Find example below.

Test:

LOAD * INLINE [

date, Forecast, Receipts, Stock on Hand, Total Stock

01-01-13, 1217, 5550, 2670, 8220

02-01-13, 1197, 1230, 7023, 8253

03-01-13, 1101, 2320, 7152, 9472

04-01-13, 1161, 1259, 8311, 9570

05-01-13, 1431, 1010, 8139, 9149

06-01-13, 1226, 970, 9723, 8893

07-01-13, 2330, 1323, 6563, 7886

08-01-13, 3002, 1789, 4884, 6673

09-01-13, 1277, 1323, 5396, 6719

10-01-13, 1045, 1456, 5674, 7130

11-01-13, 879, 1232, 6251, 7483

Expected result

date, Forecast, Receipts, Stock on Hand, Total Stock, DOS

01-01-13, 1217, 5550, 2670, 8220, 5.9

02-01-13, 1197, 1230, 7023, 8253, 5.33

03-01-13, 1101, 2320, 7152, 9472, 5.25

04-01-13, 1161, 1259, 8311, 9570, 5.29

05-01-13, 1431, 1010, 8139, 9149, 5.3

06-01-13, 1226, 970, 9723, 8893, 5.87

07-01-13, 2330, 1323, 6563, 7886, 6.1

08-01-13, 3002, 1789, 4884, 6673, 5.98

09-01-13, 1277, 1323, 5396, 6719, 5.32

10-01-13, 1045, 1456, 5674, 7130, 5.56

11-01-13, 879, 1232, 6251, 7483, 5.89

So the calculation of DOS is as follows: Stock on Hand 8253 as { 8220-1197 +1230 = 8253 and then 8253 -1101=7152 and then 7152-1161 = 5991 and then 5991 -1431=4560 and then 4560 -1226=3334 and then 3334 -2330=1004. So when it gets to the point where SOH is lower than forecast, it is divided 1004/3002=0.33} at this situation we consider 5.33 days for days of supply for day 02-01-13.

Please help.

Thanks in advance.

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 14:43:34 GMT

So essentially there is not upper limit to how many days in the future you might need to look at? If your inventory can cover up until 100 days, you will have to go 100 days ahead to find the DOS?

Re: Days of Supply logic

Anonymous — Thu, 20 Oct 2016 14:58:16 GMT

may be try with Previous() from back end .

Rangsum() in the pivot table ..

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 15:03:44 GMT

Hello Sunny,

Exactly. The horizon has no limit. We don't keep the DOS that high, but if we did, we would calculate as it is.

The logic I am using right now from the other post that gives a partial answer is below

IF(Peek([Forecast],RowID,'Temp') < [Total Stock],

1+ (([Total Stock] - Peek([Forecast],RowID,'Temp')) / Peek([Forecast],RowID+1,'Temp')),

[Total Stock] / Peek([Forecast,RowID,'Temp')) as [Days of Supply]

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 15:09:03 GMT

Can you share the complete script you are using?

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 15:21:19 GMT

Our planning system uses three main paramenters, Plant, Material (SKU), and Date.

[Temp]:

LOAD

%Plant as "Plant ID",

"%Material ID" as "Material ID",

%Date as "Date",

"Forecast",

“Receipts”,

“Stock on Hand”,

"Total Stock",

Rowno() as RowID

[DOS_final]:

LOAD

IF(Peek([Forecast],RowID,'Temp') < [Total Stock], 1+ (([Total Stock] - Peek([Forecast],RowID,'Temp')) / Peek([Forecast],RowID+1,'Temp')),

[Total Stock] / Peek([Forecast,RowID,'Temp')) as “Days of Supply”

Resident Temp

Order by “Date”

;

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 15:24:26 GMT

I don't think that this script is even close to what you are looking to get. I am going to try another way and I am going to make an assumption that the max DOS will always be less than 7 days. We can try to look to expand on to the assumption example later on.

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 15:30:39 GMT

I like that idea. The one that I using right now it is not close to what I am trying to get, as you said.

Thanks,

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 15:55:21 GMT

Check this out

The values are correct for only first two rows, but I guess as we add more data, you should start to see correct numbers

Script used:

Temp:

LOAD * INLINE [

date, Forecast, Receipts, Stock on Hand, Total Stock

01-01-13, 1217, 5550, 2670, 8220

02-01-13, 1197, 1230, 7023, 8253

03-01-13, 1101, 2320, 7152, 9472

04-01-13, 1161, 1259, 8311, 9570

05-01-13, 1431, 1010, 8139, 9149

06-01-13, 1226, 970, 9723, 8893

07-01-13, 2330, 1323, 6563, 7886

08-01-13, 3002, 1789, 4884, 6673

09-01-13, 1277, 1323, 5396, 6719

10-01-13, 1045, 1456, 5674, 7130

11-01-13, 879, 1232, 6251, 7483

];

For i = 1 to 7

Left Join (Temp)

LOAD Date(AddMonths(date, -$(i))) as date,

Forecast as Forecast$(i)

Resident Temp;

NEXT

Table:

CrossTable(Forecast_Type, Value, 5)

LOAD *

Resident Temp;

TempTable:

LOAD date,

Forecast,

Receipts,

[Stock on Hand],

[Total Stock],

If(date = Previous(date), RangeSum(Peek('CumValue'), Value), Value) as CumValue,

Value,

Forecast_Type

Resident Table

Order By date, Forecast_Type;

FinalTable:

LOAD *,

If([Total Stock] - CumValue > 0, KeepChar(Forecast_Type, '0123456789') + ([Total Stock] - CumValue)/Peek('Value')) as DOS

Resident TempTable

Order By date, Forecast_Type desc;

Right Join (FinalTable)

LOAD date,

Min(DOS) as DOS

Resident FinalTable

Where Len(Trim(DOS)) > 0

Group By date;

DROP Table Temp, Table, TempTable;

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 16:00:16 GMT

Now to expand this, I think all you need to do is change this part of the script

For i = 1 to 7

Make this as big as your realistically want this to be

For i = 1 to 100

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 18:55:23 GMT

Sunny,

I applied the changes to fit my naming standards and fit my data set, however the load freezes. It does not fail, simply freezes and I have to stop it. Find script below. Let me know if something is off according to your logic.

Forecast = TotalDemand_CS

Total Stock = TotalReceipts_CS

Forecast_Type = Demand_Type

Temp = SNP_Data

For i = 1 to 7

Left Join (SNP_Data)

Load Date(Addmonths([Date],-$(i))) as "Date",

TotalDemand_CS as TotalDemand_CS$(i)

Resident SNP_Data;

[Table]:

CrossTable(Demand_Type, Value,5)

Load

Resident SNP_Data;

TempTable:

Load

"Date",

TotalDemand_CS,

TotalReceipts_CS,

If([Date] = Previous([Date]), RangeSum(Peek('CumValue'), Value),Value) as CumValue,

Value,

Demand_Type

Resident Table

Order by "Date", Demand_Type;

FinalTable:

Load

if(TotalReceipts_CS - CumValue > 0, keepchar(Demand_Type, '0123456789') + (TotalReceipts_CS - CumValue) / Peek('Value')) as DOS

Resident TempTable

Order by "Date", Demand_Type desc;

Right Join (FinalTable)

Load

"Date",

Min(DOS) as DOS

Resident FinalTable

Where Len(Trim(DOS)) > 0

Group by "Date";

Drop Table Temp, Table, TempTable;

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 21:10:29 GMT

Can you share the script for SNP_Data table?

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 21:11:56 GMT

Sunny,

I am working with Qlik Sense. QlikView works fine. The logic works great and this is exactly what I need to do.

What do I need to change in order to make it work in Qlik Sense?

Thanks,

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 21:25:02 GMT

Seems to be working

Re: Days of Supply logic

luizcdepaula — Thu, 20 Oct 2016 21:41:00 GMT

Your script worked with the sample data now. It was a problem with the initial parameters for date in the Main tab. Once I used the default settings it worked for the sample.

I am still having trouble where my original script keeps freezing in the load process. Follow original script below. By the way, your logic is very good and I believe it will work when I get to load it properly.

[SNP_Data]:

LOAD

%Plant as "Plant ID",

"%Material ID" as "Material ID",

%Date as "Date",

"Total Demand - CS_New" as "TotalDemand_CS",

"Total Receipts - CS_New" as "TotalReceipts_CS",

"Forecast-Cases",

"Sales Order-Cases",

"Distr. Dmnd(Plnd)-Cs",

"Distr.Dmnd(Conf)-Cs",

"Distr. Dmnd(TLB)-Cs",

"Dependent Demand-Cs",

"Distr.Recit(Plnd)-Cs",

"DistrRecipt(Conf)-Cs",

"Distr. Recpt(TLB)-Cs",

"In Transit-Cases",

"Production(Conf.)-Cs",

"Prod. (Planned)-Cs",

"Stock on hand BI-Cs",

"Stock in QA-Cases",

"Sply shortage BI -Cs",

"Short code Quant.-Cs"

FROM [lib://DemandPlanningTransform/Total_SNP_Data.qvd]

(qvd);

For i = 1 to 7

Left Join (SNP_Data)

Load Date(Addmonths([Date],-$(i))) as "Date",

TotalDemand_CS as TotalDemand_CS$(i)

Resident SNP_Data;

Table:

CrossTable(Demand_Type, Value,5)

Load

Resident SNP_Data;

TempTable:

Load

"Date",

TotalDemand_CS,

TotalReceipts_CS,

If([Date] = Previous([Date]), RangeSum(Peek('CumValue'), Value),Value) as CumValue,

Value,

Demand_Type

Resident Table

Order by "Date", Demand_Type;

FinalTable:

Load

if(TotalReceipts_CS - CumValue > 0, keepchar(Demand_Type, '0123456789') + (TotalReceipts_CS - CumValue) / Peek('Value')) as DOS

Resident TempTable

Order by "Date", Demand_Type desc;

Right Join (FinalTable)

Load

"Date",

Min(DOS) as DOS

Resident FinalTable

Where Len(Trim(DOS)) > 0

Group by "Date";

Drop Table Temp, Table, TempTable;

Re: Days of Supply logic

sunny_talwar — Thu, 20 Oct 2016 22:40:20 GMT

Try changing this:

Table:

CrossTable(Demand_Type, Value,21)

Load

Resident SNP_Data;

You sample had 5 fields, but your original table have 21 fields.

Re: Days of Supply logic

luizcdepaula — Fri, 21 Oct 2016 13:40:35 GMT

Sunny,

The logic is good, however for my data might it is generating huge a data set. I removed all measures and left only the 2 important ones, Total Stock and Total Demand. It generated 754,483,464 rows, and it is still running. Any other suggestions? Thanks again for your help Sunny.

Re: Days of Supply logic

sunny_talwar — Fri, 21 Oct 2016 13:45:44 GMT

Try making this change:

For i = 1 to 7

Left Join (SNP_Data)

Load %Plant as "Plant ID",

"%Material ID" as "Material ID",

"Total Demand - CS_New" as "TotalDemand_CS",

"Total Receipts - CS_New" as "TotalReceipts_CS",

"Forecast-Cases",

"Sales Order-Cases",

"Distr. Dmnd(Plnd)-Cs",

"Distr.Dmnd(Conf)-Cs",

"Distr. Dmnd(TLB)-Cs",

"Dependent Demand-Cs",

"Distr.Recit(Plnd)-Cs",

"DistrRecipt(Conf)-Cs",

"Distr. Recpt(TLB)-Cs",

"In Transit-Cases",

"Production(Conf.)-Cs",

"Prod. (Planned)-Cs",

"Stock on hand BI-Cs",

"Stock in QA-Cases",

"Sply shortage BI -Cs",

"Short code Quant.-Cs"

Date(Addmonths([Date],-$(i))) as "Date",

TotalDemand_CS as TotalDemand_CS$(i)

Resident SNP_Data;

Next

Re: Days of Supply logic

luizcdepaula — Fri, 21 Oct 2016 14:47:20 GMT

It is still generating large data set and freezing the load. What if we limit the horizon to only 14 days forward instead of unlimited?

Re: Days of Supply logic

sunny_talwar — Fri, 21 Oct 2016 14:48:48 GMT

Right now it is limited to only 7 days ahead .

Can you share few 100 rows of data for me to test it out