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

[Former Member]

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

[swati_gawade · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

Good to know that you got solution.