I'm trying to calculate the percentage difference of two years based on the weekday of the year by using the formula below. (Sorry I am unable to include a packaged workbook due to sensitive customer information in the data)
([Average Amount of Transacstion per Weekday]
/
lookup([Average Amount of Transacstion per Weekday],-7))-1
The calculation is computed using "table across". The average "amount of transaction per weekday" is simply the average amount of transactions that occurred on each day.
Where I'm having issues is that this calculation is simply looking back 7 columns and calculating the % difference between two points. In the case below this calculation for Monday 2018 is comparing Monday 2018 to Sunday 2017 because there isn't a Friday in 2017. I want Monday to 2018 to compare to Monday of 2017. Where there is Friday in 2018 and no Friday in 2017 I want the calculation on Friday 2018 to provide a null value in the 'text' marks.
What is the best way to fix a specific weekday in 2018 to a specific weekday in 2017? Also, If there is another workaround for this I'm open to any suggestions. Any help would be very much appreciated!
Thank you
I spent days trying to figure this out....right after I posted this question I figured it out!
There's an option named "show missing values" in the "WEEKDAY(Month-Ye" pill in the columns. When I click "show missing values" it now shows Friday for 2017. Now when the formula compares the last 7 days it calculates the percentage difference for the correct weekday.
Thank you for reading anyways!