Hi,
Good-Day.
There is a challenge in populating the previous value continuously beyond once using Above() function. I have attached a sample QVF file which illustrates the problem.
Requirement:
The “Sales Goal-USA” column is calculated based on the “Yearly Sales Goal” – cumulative “Actual Sales” as of previous week divided by total number of week in year. The value in “Sales Goal-USA” is always looks at previous week’s value. When there is no “Actual Sales” in the previous week, The value in the “Sales Goal-USA” column should pick previous week’s “Sales Goal-USA” value.
Calculation is working fine except if there are repeated 0 in “Actual Sales” column.
Here are the calculations used.
Sales Goal - USA
IF(Country='USA' AND [Pending Sale]=0 AND Above(Alt([Actual Sale],0),1)=0,
Above($(v_Weekly Sales Goal-USA),1),
IF(Country='USA' AND [Pending Sale]=0 AND Alt([Actual Sale],0)=0,
$(v_Weekly Sales Goal-USA),
IF(Country='USA' AND [Pending Sale]=0 AND Alt([Actual Sale],0)>0,
$(v_Weekly Sales Goal-USA),
Sum([Pending Sale]))))
$(v_Weekly Sales Goal-USA)
IF(Country='USA' AND [Pending Sale]=0,
(($(v_Country Yearly Goal-USA) - Alt(((rangesum(Above(TOTAL Alt([Actual Sale],0),0, rowno(TOTAL))))-Alt([Actual Sale],0)),0))/(54-$(v_RowNo_Sequence))),
Sum([Pending Sale]))
$(v_Country Yearly Goal-USA)
Sum({$<Sale_Country={'USA'}>}Goal_Value)
Filter Selection in dashboard – Year: 2017, Country: USA
Note: Week 13 should be picking 1872 Similarly Week 48 should be picking 4750 for rest of the weeks as there is no “Actual Sales”
What could be wrong in the script, is there anything missing in Above function?
Have been attempting possible ways since few days, However no solution yet. Please help.
Regards,
Girish
.png "Luminary Alumni"){kind=icon}
