Hi, I coded below statement however got error expression in 100th nested if, however i need to continue code it for 365 days(number of year days. Please help, if i can use any alternative to if.
Hi, I have tried to reform below visual code in qlik by using variables for each day:
Visual basic code for percent calc is below:
Sub ModelPrep()
On Error GoTo ErrHandler
Dim r As Integer Dim c As Integer Dim campaignDay As Integer Dim plannedCtr As Long ' running counter of daily planned Dim plannedToday As Long Dim plannedTarget As Long Dim pending As Long
' clean-up... the range should ONLY include the burndown columns Range("T6:NT371").Clear
MsgBox ("Building the model... this might take up to 2-3 minutes")
Application.ScreenUpdating = False
' campaignDay = 1 ' totalPlanned = 0 ' plannedToday = 0 ' plannedCtr = 0 ' pending = 0 ' ' plannedTarget = Cells(1, 2).Value ' ' For r = 6 To 185 ' r = rows... 180 days in a typical pre-buy, row 6 is the first row of data ' ' plannedToday = Cells(1, 3).Value ' the planned number of downloads ' ' If (plannedToday + plannedCtr) > plannedTarget Then ' plannedToday = plannedTarget - plannedCtr ' End If ' ' plannedCtr = plannedCtr + plannedToday ' ' campaignDay = campaignDay + 1 ' ' Next r
For r = 6 To 371 '185 ' r = rows... 180 days in a typical pre-buy rr = r For c = 20 To 385 '186 Cells(rr, c) = Cells(5, c).Value * Cells(r, 5) ' Cells(5,c) is the model! rr = rr + 1 If rr > 371 Then Exit For Next c Next r
' ------------------------------------------------------------------ ' Set cell formatting...
Set Day1ML = $(Day1M); Set Day2ML =$(Day1ML)+$(Day2M); Set Day3ML = $(Day2ML)+ $(Day3M);
:
:
Set Day1M = ($(Planned_Total_Loaded)*$(pct1))/100; Set Day2M = ($(Planned_Total_Loaded2)*$(pct2))/100; Set Day3M = ($(Planned_Total_Loaded3)*$(pct3))/100;
:
:
Set Planned_Daily_Loaded = IF((Target_VIN_Load_Volume)> (Daily_VIN_Load_Capacity), (Daily_VIN_Load_Capacity),(Target_VIN_Load_Volume)); set Planned_Total_Loaded = (Planned_Daily_Loaded); set Planned_Daily_Loaded2 = IF(((Planned_Total_Loaded) + (Daily_VIN_Load_Capacity)) <= (Daily_VIN_Load_Capacity),(Daily_VIN_Load_Capacity),((Target_VIN_Load_Volume)-(Planned_Daily_Loaded))); set Planned_Total_Loaded2 = (Planned_Total_Loaded) + (Planned_Daily_Loaded2); set Planned_Daily_Loaded3 = IF(((Planned_Total_Loaded2) + (Daily_VIN_Load_Capacity)) <= (Daily_VIN_Load_Capacity),(Daily_VIN_Load_Capacity),((Target_VIN_Load_Volume)-(Planned_Total_Loaded2))); set Planned_Total_Loaded3 = (Planned_Total_Loaded2) + (Planned_Daily_Loaded3);
@Abhineo try below. I have assumed the data field names.
Data:
LOAD Day, // Date or day number
Pct, // stores $(pct1).... $(pct365).. values
Target_VIN_Load_Volume,
Daily_VIN_Load_Capacity
FROM table;
Final:
load *,
(Measure*Pct)/100 as [Percent]
load *,
if(rowno()=1,
if(Target_VIN_Load_Volume> Daily_VIN_Load_Capacity,Daily_VIN_Load_Capacity,Target_VIN_Load_Volume),
rangesum(peek(Measure),if(peek(Measure)+Daily_VIN_Load_Capacity<=Daily_VIN_Load_Capacity,
Daily_VIN_Load_Capacity,Target_VIN_Load_Volume-peek(Measure)))
) as Measure
Resident Data
order by Day;
drop table Data;
Excellent Kush, Its pretty close and thanks for same. Input are little different and in millions, also model_start_date matching required so, joined with date of input stream and in order with Daynum, script changed to using DayNum , instead Rowno(), so pretty close results are coming. Adding some stuffs to get exact figures. Your code was really helpful and tanks again.
