Coverage of Supplies with respect to Stock and Demand

[#1]

Hi Guys,

I think I am not upto this task to build this in Qlikview. I will try lasttime here before giving up. May be John or someother Experts here may help.

As given in the attached ExcelSheet I need to find the Coverage of the days of Suppy(For Ex value 26 in the attached Excelsheet.) for the ComingWeeks.

This 26 is for present Year-week:201115. It will change from week to week! So it must be Dynamic.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/Example-Shortage-Reporting.xlsx]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/ShortageReporting_5F00_Forum.qvw]

Thanks in advance

sravan

[#2]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7485.COVERAGE-Calculation.docx:550:0]

It looks a little bit complicated but the process of Coverage looks is as given in this Document.

regards

Sravan

[#3]

Any ideas??

[#4]

May be someone can help me. I dont want to give up so easily!

I need only two steps to get my code right.

Calculation of Coverage based on 7 days per week:

Script Calculate Coverage()

For i = 0 to "End of Periods"
 Stock = ST(i)

If Stock <= 0 Than
 CV(i) = 0
 Stop Script
 End If

 Demand = 0
 Cover = 0

 For j = i+1 to "End of Periods"
Demand = Demand + DM(j)
 If Demand >= Stock Than
 Demand = Demand - DM(j)
 Rest = Stock - Demand
 If Rest > 0 Than
 Cover = Cover + (7 * Rest / DM(j) )
 End If
 CV(i) = Cover
 Stop Script
 Else
 Cover = Cover + 7
 End If
 End For

 If Stock > Demand Than
 Cover = 999
 End if

End For

End Script

First Question:

I need to give this "End of Periods"

In Qlikview I defined the Period as a variable vLastWeek

Now for the for loop If I want to get the next 10 weeks assume

I did like this:

For i= 0 to ($(vLastWeek)+ 10)-1

It does not work. How to do this?

Second Question:

Sum ([KSTKBAL]) is Stock Balance aggregated to weeks and Products in the chart. I need to make this as Stock in the script. How to do this?

Thanks in advance

Sravan

[#5]

The First Problem is solved where I take

For i= $(vLastWeek) to ($(vLastWeek)+3) Though it has a problem when the week changes from 201052 to 201101

For Second Question I am still seeing a solution