Skip to Content
avatar image
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 comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    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 comment
    10|10000 characters needed characters exceeded

    • Former Member Zahid Yener

      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,

  • 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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 19, 2015 at 06:33 AM

    Hi,

    Is there any comment?

    Best Regards,

    Add comment
    10|10000 characters needed characters exceeded