Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
=if (Date = Model_Start_Date, $(Day1ML),
IF(Date=Date(Model_Start_Date+1),($(Day2ML)),
:
Model_Start_Date))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) )))))))))) ))))))))))) )))))))))) ))
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...
Range("T6:GQ186").Select
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("T6:GQ186").Select
Selection.NumberFormat = "0"
Range("A1:B1").Select ' Unselects the model region and places cursor in upper left-hand corner
' ------------------------------------------------------------------
Application.ScreenUpdating = True
MsgBox ("The model is complete")
Exit Sub
ErrHandler:
MsgBox (Err)
Application.ScreenUpdating = True
End Sub
Did code like this for each 356 days
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 Calculation could be done in script level to simplify the expression. I will try to formulate.
Thanks @Kushal_Chawda . Appreciate your quick response.
@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.
I should get below final output:
Planned Total Complete |
0 |
2 |
5 |
9 |
14 |
20 |
27 |
37 |
49 |
61 |
70 |
78 |
86 |
96 |
111 |
127 |
178 |
258 |
344 |
447 |
563 |
705 |
900 |
1115 |
1322 |
1503 |
1651 |
2067 |
2457 |
2788 |
3081 |
3307 |
3488 |
3646 |
3802 |
3954 |
4093 |
4217 |
4362 |
6183 |
8822 |
12319 |
17415 |
23161 |
28798 |
34292 |
40513 |
47316 |
54971 |
64405 |
76526 |
91682 |
109781 |
128808 |
147419 |
166983 |
189598 |
216082 |
246062 |
275944 |
302511 |
326890 |
346056 |
360813 |
373081 |
383948 |
393927 |
403613 |
413501 |
425069 |
439979 |
458572 |
479814 |
504337 |
530648 |
557619 |
586287 |
617578 |
652452 |
691744 |
735117 |
772854 |
811127 |
851801 |
894740 |
939223 |
983476 |
1026403 |
1065998 |
1101558 |
1134395 |
1165062 |
1193504 |
1220019 |
1244664 |
1267535 |
1288710 |
1308880 |
1328216 |
1346943 |
1365465 |
1383932 |
1401883 |
1419228 |
1437573 |
1457662 |
1479058 |
1501176 |
1531680 |
1562191 |
1590127 |
1616488 |
1640647 |
1662139 |
1684352 |
1709538 |
1734400 |
1757654 |
1777605 |
1794163 |
1808201 |
1820748 |
1832173 |
1842720 |
1852635 |
1862014 |
1870886 |
1879407 |
1887713 |
1895750 |
1903500 |
1911019 |
1918276 |
1925264 |
1932052 |
1938654 |
1945052 |
1951241 |
1957304 |
1963233 |
1968996 |
1974565 |
1980038 |
1985368 |
1990548 |
1995586 |
2000562 |
2005343 |
2009955 |
2014443 |
2018816 |
2022996 |
2027068 |
2030988 |
2034737 |
2038318 |
2041793 |
2045099 |
2048311 |
2051389 |
2054336 |
2057082 |
2059656 |
2062174 |
2064588 |
2066869 |
2069055 |
2071139 |
2073121 |
2074995 |
2076805 |
2078557 |
2080258 |
2081850 |
2083376 |
2084815 |
2086222 |
2087558 |
2088828 |
2090043 |
2091222 |
2092345 |
2093394 |
2094393 |
2095370 |
2096209 |
2096988 |
2097732 |
2098434 |
2099088 |
2099687 |
2100242 |
2100733 |
2101184 |
2101599 |
2101998 |
2102374 |
2102719 |
2103022 |
2103309 |
2103589 |
2103862 |
2104127 |
2104388 |
2104638 |
2104872 |
2105097 |
2105317 |
2105538 |
2105758 |
2105978 |
2106198 |
2106418 |
2106638 |
2106858 |
2107078 |
2107298 |
2107519 |
2107739 |
2107957 |
2108176 |
2108393 |
2108608 |
2108823 |
2109037 |
2109250 |
2109462 |
2109673 |
2109882 |
2110088 |
2110292 |
2110492 |
2110687 |
2110879 |
2111068 |
2111254 |
2111435 |
2111611 |
2111780 |
2111946 |
2112111 |
2112272 |
2112434 |
2112595 |
2112756 |
2112917 |
2113076 |
2113236 |
2113394 |
2113550 |
2113702 |
2113851 |
2113996 |
2114136 |
2114273 |
2114405 |
2114533 |
2114656 |
2114771 |
2114880 |
2114981 |
2115081 |
2115175 |
2115261 |
2115341 |
2115415 |
2115483 |
2115547 |
2115607 |
2115665 |
2115720 |
2115772 |
2115822 |
2115871 |
2115918 |
2115963 |
2116008 |
2116051 |
2116093 |
2116133 |
2116172 |
2116210 |
2116247 |
2116282 |
2116316 |
2116346 |
2116374 |
2116400 |
2116418 |
2116433 |
2116447 |
2116458 |
2116468 |
2116477 |
2116483 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
2116485 |
for each day Input load is:
1 |
12 |
9 |
10 |
13 |
11 |
17 |
34 |
25 |
8 |
2 |
8 |
18 |
26 |
38 |
31 |
271 |
246 |
119 |
240 |
223 |
373 |
545 |
375 |
219 |
122 |
77 |
2215 |
86 |
136 |
150 |
144 |
109 |
77 |
156 |
168 |
83 |
43 |
309 |
12357 |
6498 |
9025 |
15100 |
10948 |
7178 |
7184 |
13987 |
12931 |
15168 |
22302 |
31150 |
36471 |
39127 |
29118 |
23623 |
34707 |
50132 |
57001 |
57237 |
36435 |
18606 |
26398 |
11 |
14 |
3590 |
6907 |
5908 |
7458 |
10220 |
21271 |
33750 |
38780 |
36331 |
46687 |
40661 |
38016 |
46572 |
55014 |
64315 |
73312 |
76125 |
12357 |
60889 |
68057 |
70884 |
60882 |
52159 |
48851 |
34384 |
24697 |
27735 |
25810 |
20311 |
17573 |
16552 |
13724 |
11307 |
13173 |
12336 |
12356 |
14037 |
14850 |
11579 |
10513 |
21917 |
26879 |
25287 |
22605 |
81367 |
24616 |
15340 |
19450 |
18664 |
9894 |
27190 |
41112 |
20704 |
12642 |
13554 |
12612 |
10625 |
14751 |
16336 |
12832 |
10445 |
14014 |
14833 |
13469 |
14633 |
14197 |
10553 |
7490 |
11486 |
10912 |
8966 |
5883 |
8724 |
8382 |
6519 |
6319 |
5206 |
6050 |
10416 |
8368 |
6880 |
5054 |
5157 |
5732 |
4666 |
4371 |
4837 |
4341 |
3577 |
3742 |
3303 |
4158 |
3420 |
3487 |
3212 |
2640 |
2986 |
2503 |
1508 |
2421 |
2609 |
2215 |
1657 |
2147 |
2039 |
1366 |
2051 |
2018 |
2105 |
1829 |
2530 |
2353 |
2367 |
2384 |
2678 |
2517 |
2202 |
2895 |
3073 |
2844 |
2660 |
2772 |
2025 |
1847 |
2244 |
2176 |
2120 |
1984 |
1732 |
1866 |
1934 |
1933 |
1762 |
1859 |
1639 |
1703 |
1857 |
1694 |
1789 |
1510 |
1299 |
1377 |
1333 |
1254 |
1069 |
1187 |
1063 |
1170 |
1010 |
1118 |
1048 |
940 |
1126 |
1063 |
1064 |
988 |
1071 |
1041 |
904 |
1012 |
1011 |
939 |
920 |
1044 |
922 |
803 |
987 |
969 |
902 |
812 |
848 |
726 |
772 |
803 |
781 |
698 |
700 |
673 |
642 |
513 |
723 |
520 |
2198 |
2262 |
552 |
525 |
719 |
442 |
729 |
423 |
439 |
2273 |
2201 |
15977 |
16834 |
16703 |
62113 |
7950 |
1893 |
259 |
1040 |
551 |
232 |
241 |
226 |
221 |
243 |
209 |
189 |
193 |
209 |
197 |
211 |
516 |
15618 |
408 |
387 |
208 |
173 |
644 |
1156 |
1091 |
23482 |
1615 |
190 |
163 |
1373 |
276 |
218 |
269 |
221 |
211 |
194 |
222 |
2782 |
228 |
198 |
67629 |
55631 |
272 |
89824 |
343 |
1624 |
1319 |
46741 |
61780 |
383 |
1335 |
367 |
298 |
273 |
336 |
71416 |
312 |
337 |
338 |
5817 |
319 |
322 |
310 |
268 |
776 |
275 |
248 |
302 |
279 |
267 |
261 |
298 |
267 |
227 |
261 |
257 |
251 |
35473 |
311 |
310 |
227 |
288 |
291 |
310 |
289 |
276 |
217 |
209 |
225 |
250 |
276 |
257 |
280 |
218 |
170 |
524 |
903 |
231 |
222 |
599 |
213 |
190 |
223 |
238 |
199 |