Percentage of weeks through the year

[zahirramcharan]

Hi there,

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.

Any help would be much appreciated.

Thanks,

Zahir

[sunny_talwar]

May be use Max() instead of RangeMax()

=If(Week(Today(1)) < Max(Week), Week(Today(1))/Max(Week), 100)

[ahaahaaha]

Hi Zahir,

The idea of computation is not very clear. As I understand, if you want to show the estimated percentage of sales for today, then the calculations should be based on the formula

=Week(Today())/52  (share, if in percentage, need multiply by 100.

More accurate calculations make sense, because it's still a forecast).

If the forecast is based on selection, then today date does not play any role and it is necessary to calculate a maximal week of selection, like this

=If(GetSelectedCount([FieldDate])>0,  Week(Max([FieldDate])))/52 (in year 52 weeks).

I'm sorry if I'm wrong. Maybe I just did not understand your idea.

Regards,

Andrey

[zahirramcharan]

Hi,

I have tried that but it hasn't worked unfortunately!

Many thanks though!

[zahirramcharan]

Hi Andrey,

Thank you for the quick repose!

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.

Does that make sense?

Sorry for the confusion!