I have a combo graph detailing 2 types of sales across a 52 week period for the last 3 years. I have created an expression to show the percentage of sales so far this year of last year (Sum(Year3SalesIn)/Sum(Year2SalesIn)).
However, I am also trying to create an expression in the footnote to show the target sales for this point in the year to give the figure some context. For example, we are currently in week 21 of 52, therefore sales should be around 40% of last year in theory.
I have opted to write an if statement as follows: =If(week (today (1))<rangemax(Week), Week(today (1)) / rangemax(Week), 100). I've intended to structure it so that If the current week is smaller than the maximum week number in the selection it divides the current week number by the number of weeks selected. If the current week number is greater than or equal to the maximum week number of the selection, it returns 100 as we the target for that period would have been 100% of the last period and it is now over and closed.
My formula isn't working, and I can't even get the rangemax function alone to return the maximum week number contained in my selection.
I am also aware this wouldn't work if weeks were selected that are not continuous, so would also prefer to have a count of the number of weeks instead of taking the maximum number of weeks.
It's not necessarily a forecast, more of a reference figure to show how much of last years total sales we could have excepted to already achieve. Week(today)/52 shows the current percentage of the year we have covered, but I would prefer if when for example I selected the first 10 weeks of the year, it showed the target to be 100% because we have covered all of those first 10 weeks. If I have selected weeks 15-30, it should show 37.5% because we are in week 20 and have covered 6/16 weeks in the aforementioned range.