Creating WEBI Formula From BEX Structure

[Former Member]

Hi Experts,

My data source is from BEX and I am generating a WEBI Report and its graph.

Structure in BEX includes customer exit function with Curent Year and Last Year with offset.

I have two cases that I want to solve.

1- To sort the countries according to Current Year and their Net Weights.

2- To show first five Countries with upper ranking and sixth and other ranks will be OTHER.

Please consider the values of Structure can change dynamically.

I used Min and Max function but they were not solution.

Is there any suggestion to  get result I have wanted below screenshots?

Best Regards,

[former_member184594 · Accepted solution]

Hi Cihangir,

This is how I did it.

1. Create a Current Year Net Wieght variable:

=[Net Weight] where([Structure]=FormatDate(CurrentDate();"yyyy"))

2. Create a Current Year Ranking variable:

=Rank([Current Year Net Weight];([Country]);Top)

3. Create your Cross Tab. Country by Year on Net Weight

4. Right click on the cross tab and select "Add filter".

5. Create a filter like below:

Your cross tab will be like below. You will only have Top 5 countries.

6. Now we have to add OTHERS part to the table. For this right click on the table, select "Format Table", check "Show bottom footer" box and click on "OK" button.

7. Now you add the following formula to the bottom of your table.

=NoFilter([Net Weight])-Sum([Net Weight])

This formula will give you OTHERS.

8. Now if you want the TOTAL of all countries you add another row to the bottom of your table and add the following formula.

=NoFilter([Net Weight])

This will give you the total of your Net Weight for all countries and your table will be look like:

9. As you can see above the countries are not sorted from top to bottom according to last year. To do that:

Create a variable C.Y. Rank Dim

=[Current Year Ranking]+" "

10. Put it on table like below:

11. Right click on the ranking cloumn and select "Hide Dimension" option.

12. Your final table will look like the one below:

Have a nice one.

[Former Member · Accepted solution]

Hi Zahid,

Thank you very much.

It works.

I apreciate your detailed and well-documented solution.

My Best Regards,

Cihangir

[Former Member · Accepted solution]

Hi Zahid,

I have one question. If users selected 2013 year, formula (=[Net Weight] where([Structure]=FormatDate(CurrentDate();"yyyy"))) would not work.  Becuase, Current Year is showing the 2015 for now.

Which formula can I use according to CurrentDate() function.

Best Regards,

[former_member184594 · Accepted solution]

If the user is answering to some sort of prompt, you can use UserResponse formula to get the year entered by a user.

=UserResponse("<Whatever your prompt text is:>")

This would do it.

Have a nice one.

[Former Member · Accepted solution]

Hi Zahid,

I have thought simply and I find a solution replacing to the CurrentYear() function.

Below formula get me true result.

=[Net Weight] Where([Structure]=Right([UserResponse];4))

Thanks,

My Best Regards,

[sateesh_kumar1]

Hi Cihangir ,

It would be simple if you can remove structure and create key figures for the structure elements .

Ex : Sales - Current Year and Sales - Last Year. and so on  .Apply rank on Current Year key figure.

[Former Member]

Hi,

Is there any comment?

Best Regards,