cancel
Showing results for 
Search instead for 
Did you mean: 

Bex calculate Difference % between two dates for the same key figure

Former Member
0 Kudos
233

Hello Experts,

My sincere apology if this question has been asked quite a few times. I just could't find it anywhere. I have a key figure 'Sales Revenue' which is being displayed separately for the  different fiscal year week. Lets say there are two fiscal year week 42.2016 & 42.2015. Now I want to calculate the diff % which will be like this (sales Rev w.r.t 42.2015  - sales Rev w.r.t 42.2016 ) / ( sales Rev @ 42.2015 )   * 100. However I don't know how to achive this in Bex since the sales Rev w.r.t 42.2015 and sales Rev w.r.t 42.2016 are the same key field.

I hope my question is clear. Please let me know if you want me to explain it more.

Best Regards

View Entire Topic
swati_gawade
Contributor
0 Kudos

Hi Anik,

You might be able to make use of cell references and cell formula for this.

That is, you can create 2 structures in your query. then in your column structure, create 2 selections on sales revenue, one for current year fisc week and one for last year fisc week.

Then create 3rd column for your percentage.

In the top menu bar, click on cell restriction button. Create cell reference for 2 sales revenue columns and for 3rd column create a cell formula. enter your formula for percentage here using the cell references created.

Please share your overall report structure for more clarity.

Hope this helps.

-Swati.

Former Member
0 Kudos

What  Former Member suggested is okay till first paragraph. But in this case no need of Cell Reference.

You need to create a structure with 2 selections and a formula based on these 2 selections.

Former Member
0 Kudos

Hi Swati & Sucheta,

Both of you pointed me to the correct direction. I solved it as follows, created key figures as selection with customer exit variables on calday and in the exit I derived the date range. So for each week (here it is two weeks)  I created a different customer exit variable.

Thanks for your answers.

Regards

Former Member
0 Kudos

Good to know that you got solution.