Days of Supply logic

[luizcdepaula]

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.

LD

[luizcdepaula]

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;

Next

[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;

[sunny_talwar]

Can you share the script for SNP_Data table?

[luizcdepaula]

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,

LD

[sunny_talwar]

Seems to be working

[luizcdepaula]

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;

Next

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;

[sunny_talwar]

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.

[luizcdepaula]

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.

[sunny_talwar]

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

[luizcdepaula]

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?

[sunny_talwar]

Right now it is limited to only 7 days ahead .

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