Dynamically Controlling Top/Bottom N values using Sap HANA
There will be certain situations where we would need to rank a column dynamically for top/bottom N values as per the requirement of Customer. So, in this blog post I have discussed the workaround for this scenario.
While developing a Sales Analysis Dashboard I had to rank top ‘n’ Sales values based on the Product names which should happen dynamically as its required by my Customer. In this case, we were thinking of a solution for this scenario.
So, in order to do ranking dynamically in this case, I had to search for a workaround. So, I tried to handle this in a calculation view in HANA by using an input parameter as a threshold of a ranking column. For this, kindly follow the steps mentioned below:
Firstly, I have a data of more than 200 line items which I have to rank dynamically according to the input given by the user.
Here I have to rank the column – “Sales” in Top N wise dynamically based on the input.
So I have kept the Input Parameter Field as Threshold and Sort Direction as Descending.
Once after doing it, Activated and checked Data Preview. The Column “Sales” got ranked based on the Input given by us.
Thus, I have ranked my desired column in Top N order.
For adapting the same in Visualisation tool, I have used Xcelsius here using Slider and Chart Components. (Sample video and screenshots below)
So, from this blog post you could have got an idea on how to do dynamic ranking for Top/Bottom values to a maximum extent as per the requirement. Please feel free to share your views and ideas on the same and also share if you have any workarounds in such kind of case.
In the above example I want to understand how did you pass the input parameter in the threshold. Since I am trying to do this in a similar situation but when I go to Rank node and try to select the threshold type to Input Parameters I can't see the input parameter (screenshot attached)
Your help is appreciated
screenshot of the calculation view