
1. Re: Lookup / Previous_Value / Table Calculations
Sankarmagesh Rajan Jul 5, 2016 2:52 AM (in response to Joe Bend)hi Joe,
if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+ lookup(SUM([Product]),1)LOOKUP(sum([Forcast]),1 )
else sum([Stock])
END
Use calc in view and change compute using week then get desired output.
Thanks
sankar

2. Re: Lookup / Previous_Value / Table Calculations
Mark Fraser Jul 5, 2016 2:48 AM (in response to Joe Bend)Hi Joe
Here with my version  (I think this is how you want to calculate it...)
2 versions (depends if you need to fill the week 27 stock)
With (as above) =
IF INDEX() == 1 THEN SUM([Stock]) ELSE ( LOOKUP(RUNNING_SUM(SUM([Stock])),1) + LOOKUP(RUNNING_SUM(SUM([Product])),1) )  LOOKUP(RUNNING_SUM(SUM([Forecast])),1) END
Without =
( LOOKUP(RUNNING_SUM(SUM([Stock])),1) + LOOKUP(RUNNING_SUM(SUM([Product])),1) )  LOOKUP(RUNNING_SUM(SUM([Forecast])),1)
Enjoyable problem
v9.3.3. attached.
Cheers
Mark

210430.twbx 15.2 KB


3. Re: Lookup / Previous_Value / Table Calculations
Mark Fraser Jul 5, 2016 2:58 AM (in response to Sankarmagesh Rajan)Hi Sankar
I followed your instructions
Although week 27 is correct, the rest of the weeks just add increments of 10?
Is this your expected output?
I'm no so sure, becuase Joe gave a clue here, as to his expectations...
Try in Excel (copy attached)
Cheers
Mark

Book1.xlsx 8.6 KB


4. Re: Lookup / Previous_Value / Table Calculations
Sankarmagesh Rajan Jul 5, 2016 3:04 AM (in response to Mark Fraser)1 of 1 people found this helpfulHi Mark,
I have edited my calc now in first reply. Pls try once.
if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+ lookup(SUM([Product]),1)LOOKUP(sum([Forcast]),1 )
else sum([Stock])
END
we both answered same question now with different way.
Mark rocks
Thanks
sankar

5. Re: Lookup / Previous_Value / Table Calculations
Mark Fraser Jul 5, 2016 3:20 AM (in response to Sankarmagesh Rajan)Thank you Sankar, that's now working as expected, although need to spell forecast correctly...
if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+ lookup(SUM([Product]),1)LOOKUP(sum([Forecast]),1 )
else sum([Stock])
END
Apologies, I didn't mean to pick on you  its nothing personal.
I just think it's important we (all contributors, me included) test solutions first...
Sankar rocks

6. Re: Lookup / Previous_Value / Table Calculations
Sankarmagesh Rajan Jul 5, 2016 3:44 AM (in response to Mark Fraser)hi Mark,
Sure thanks
I also learned new calc from your reply..
Thankssankar