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,
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.
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