Skip to Content
author's profile photo Former Member
Former Member

Creating WEBI Formula From BEX Structure

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,

BEX.jpg (20.4 kB)
BO-1.JPG (22.6 kB)
BO-2.JPG (19.4 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Oct 19, 2015 at 08:06 AM

    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.


    pic_1.png (41.9 kB)
    pic_2.png (31.3 kB)
    pic_3.png (13.6 kB)
    pic_4.png (36.5 kB)
    pic_5.png (19.5 kB)
    pic_6.png (23.5 kB)
    pic_7.png (58.5 kB)
    pic_8.png (18.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 19, 2015 at 11:24 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2015 at 06:33 AM

    Hi,

    Is there any comment?

    Best Regards,

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.