10 Replies Latest reply on Jul 23, 2018 11:45 AM by Tony Smith

# Variance - compare the current five weeks data with the previous year's five weeks

Hi All,

I want to show the current five weeks sales and the same five weeks for the previous year based on the 'BBCA weeks in fin year' field.

I want to compare the current five weeks data with the previous year's five weeks transaction sales.

Also, compare the current five weeks of the budget to the previous year transactions sales

I have attached a spreadsheet to illustrate what I am trying to achieve.

I used the Report Date field to recognise the current BBCA week in fin year.

For example today is 21st July 2018, so the current BBCA week in fin year is 1920 because we now have a completed week according to the BBCA week in fin year date.

I have attached twbx package that shows the details of the BBCA week in fin year date.

Thanks

Regards

Tony

• ###### 1. Re: Variance - compare the current five weeks data with the previous year's five weeks

Tony,

Thanks,

Ankit Bansal

• ###### 2. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Ankit,

Thank you for all your help.

I got the error message when I tried to open the attachment:
Errors occurred while trying to load the workbook. The load was not able to complete successfully.

I am using version 10.5. Can you please re attach the files.

Regards

Tony

• ###### 3. Re: Variance - compare the current five weeks data with the previous year's five weeks

Here it is.

• ###### 4. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Ankit

How do I calculate the year on Year growth for each of the weeks in the fin year.

I.e (1920 / 1820 -1)

Also I want to do the same calculation for Budget data. I want to calculate the budget data in current week in fin year with the transaction sales data for the same week in the last fin year in fin year  i.e 1920 budget data / 1820 - 1 transactions sales.

At the moment the week in fin year filter in the budget worksheet is done manually.  Is it possible to use a formula to get the sales values?

Please see spreadsheet for illustrations of what I am trying to achieve.

Thank you

Regards

Tony

• ###### 5. Re: Variance - compare the current five weeks data with the previous year's five weeks

Tony,

please see worksheet "Transaction final data" in twbx i have shared. There I have not manually added the filter for last 5 weeks. It is getting derived as per today's date.

Same logic can be applied to budget data as well.

Thanks,

Ankit Bansal

• ###### 6. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Ankit,

I have seen the "Transaction final data" worksheet and the filter was not manually added to the filter that is good.

How do I calculate the year on Year growth for each of the weeks in the fin year.I.e (1920 / 1820 -1)

Also I want to do the same calculation for Budget data. I want to calculate the budget data in current week in fin year with the transaction sales data for the same week in the last fin year in fin year  i.e 1920 budget data / 1820 - 1 transactions sales.

Thank you

Regards

Tony

• ###### 7. Re: Variance - compare the current five weeks data with the previous year's five weeks

Use this formula for YOY:

Make sure you compute along table (across)

• ###### 8. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Ankit

Thank you for all your work on this.

The formula you sent me works for the transaction data year on Year growth.

How do I calculate the budget data in the current week in fin year with the transaction sales data for the same week in the last fin year.  i.e1920 budget data /1820 - 1transactions sales.

Regards

Tony

• ###### 9. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Tony,

Please see the attached workbook. I have added budget YOY(budget/transaction-1) in the transaction final data worksheet itself as detail.

You basically need to define relationship first between transaction and budget data(known as data blending).

Then use the below formula do derive budget YOY.

Hope it works for you.

• ###### 10. Re: Variance - compare the current five weeks data with the previous year's five weeks

Hi Ankit,

This is perfect! Thank you so much.

Regards

Tony