traffic: LOAD domain, date(num#(month_year),'MMM-YYYY') as month_year, percentage FROM [Traffic Numbers.xlsx] (ooxml, embedded labels, table is Sheet1); join LOAD domain, date(num#(month_year),'MMM-YYYY') as month_year, [actual traffic] FROM [Traffic Numbers.xlsx] (ooxml, embedded labels, table is Sheet2); Final: Load domain, month_year, percentage, [actual traffic], Num(If(domain <> Previous(domain),[actual traffic], Peek('projectedtraffic')*(1+percentage)),'#,##0.0') as [projectedtraffic] Resident traffic Order by domain, month_year; Drop Table traffic;
I have found that this type of problem lends itself nicely to a recursive solution. Unfortunately I have no clue how to code a recursive solution in QLIK so I typically do these operations in my load script. If you are pulling from a SQL Server database you could use TSQL code like the example below.
DECLARE @TblVar TABLE (tv_Domain nvarchar(15), tv_MonthYear date, tv_Percent float, tv_Traffic float)
INSERT INTO @TblVar
;With TblVar_cte As
Select tv_Domain, tv_MonthYear, tv_Percent, tv_Traffic
WHERE tv_MonthYear = '12/01/2017'
Select cte.tv_Domain, DateAdd(month,1,cte.[tv_MonthYear]) as tv_MonthYear, tv.tv_Percent, (tv.tv_Percent* cte.tv_Traffic) + cte.tv_Traffic as [tv_Traffic]
From TblVar_cte as cte INNER JOIN @TblVar as tv on DateAdd(month,1,cte.[tv_MonthYear]) = tv.[tv_MonthYear]
Where cte.tv_MonthYear < '5/01/2018'
If you want to do this on the front end... you can try this
=Sum(TOTAL <domain> [actual traffic]) * exp(RangeSum(Above(Log(1+percentage), 0, RowNo())))
Example.qvw 157.8 K