topic Re: Bottom Selling Product Dynamically in App Development

Bottom Selling Product Dynamically

olaoyesunday — Fri, 10 Nov 2023 09:48:53 GMT

Hi All,

I wanted to dynamically calculate the bottom-selling product in my application. Let's say I want the 10 bottom-selling products based on sales amount. I created a variable to be used in my input box, named vBottomSellingProducts. In my straight table, I chose 'Product Name' as my dimension, and I used 'Sales Amount' as my measure. I used the following formula in my expression:

=aggr(if(rank(sum([Sales Amount])) <= vBottomSellingProducts, sum([Sales Amount]), null()), [Product Name]) as Amount

Then, I used it in the sort tab on 'Product Name' in ascending order. If I insert 10, for example, into my input box, it would start from the smallest price against the product, say $3,971,546.00, in ascending order. However, if I enter a higher figure, it would start from a price lower than the 3 million above say 242,894.96, in ascending order, as shown in the two screenshots below, if I enter 1 it would give me higher figure 225,357,584.50 as also shown below which is not what I wanted.. Please, what is the correct formula to use to give me the bottom-selling products based on sales amount, in case I enter 1, 2, 5 or 50 etc products in my input box, so that it will still give me the same bottom-selling products?"

10 bottonmselling product1 bottom selling product50 bottom selling product

Re: Bottom Selling Product Dynamically

vincent_ardiet_ — Fri, 10 Nov 2023 10:03:03 GMT

Try this:
sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=vBottomSellingProducts"}>} [Sales Amount])

Re: Bottom Selling Product Dynamically

olaoyesunday — Fri, 10 Nov 2023 10:36:27 GMT

@vincent_ardiet_ Thanks very much, I used the formular above is still giving me the same thing. or is there anything I ought to check or uncheck in my Sort tab?

Re: Bottom Selling Product Dynamically

vincent_ardiet_ — Fri, 10 Nov 2023 10:40:49 GMT

Try to add this measure and see what this is returning
Rank(sum([Sales Amount]))

Re: Bottom Selling Product Dynamically

olaoyesunday — Fri, 10 Nov 2023 11:01:17 GMT

After applying the formular, It was returning these in my screenshot below:

Re: Bottom Selling Product Dynamically

vincent_ardiet_ — Fri, 10 Nov 2023 11:04:17 GMT

Ok, so now if you replace with this expression (hardcoded rank threshold):

sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=10"}>} [Sales Amount])

Re: Bottom Selling Product Dynamically

olaoyesunday — Fri, 10 Nov 2023 11:20:39 GMT

When I applied the formular, it returns what is shown in the screenshot below, but when I put one in the formular sum({<[Product Name]={"=Rank(sum([Sales Amount]))<=1"}>} [Sales Amount]) it returns the number I ringed red, which obviously is not what I wanted.

Re: Bottom Selling Product Dynamically

vincent_ardiet_ — Fri, 10 Nov 2023 13:13:21 GMT

Sorry I've noticed you want them in ascending order. So just multiply by -1 the sum in the rank:
sum({<[Product Name]={"=Rank(-sum([Sales Amount]))<=vBottomSellingProducts"}>} [Sales Amount])

Re: Bottom Selling Product Dynamically

olaoyesunday — Fri, 10 Nov 2023 14:09:01 GMT

@vincent_ardiet_

Thanks very, it works!