Summing Firstsortedvalue

[NickBentley]

I am using the below expression to determine the top selling item in a straight table.

FirstSortedValue([Short Name],-[Sales_Value],1)

This works fine for individual sales weeks.  However, when multiple weeks or the entire sales year is selected, the result that is given is just the highest sales value for an individual week .

Is it possible for a number of weeks to be summed (e.g. sales for weeks 20 to 25) and then the total of these weeks to be sorted by highest selling value? 

If possible I would also like the weeks / year to be dynamic and not hard coded with set analysis.

Thanks for your help! 

[edwin]

can you attach your QVW?

[NickBentley]

Hi Edwin,

My QVW contains sensitive data so I am unable to share. 

I have been able to use the following expression to successfully get sales value to accumulate over dynamic weeks and then show the highest individual accumulation (max figure):

max(Aggr(Sum(Sales_Value),[Item Name],Store Name))

I have also used the next expression (below) to succesfully show the item name of the highest accumulated figure:

=FirstSortedValue([Item Name], -Aggr(Sum(Sales_Value), [Item Name]),1)

However, my challenge now is to get the sum of the associated units sold (Qty) to that item over the same period, this is something I can't seem to work out as the units need to also accumulate. 

Thanks

[NickBentley]

I believe the solution I am now after is to find the 'Firstsortedvalue' of the results of a previous 'Firstsortedvalue'.

I am using Firstsortedvalue to find the item with the highest value sales amount, and now I need to find the quantity (units) associated with that sales amount. If I run firstsortedvalue on units alone it gives the item with highest units sales which is not often the same as the item with the highest value sales amount.

[tresesco · Accepted solution]

Give a try like:

Sum(

{<[Item Name]={'$(=FirstSortedValue([Item Name], -Aggr(Sum(Sales_Value), [Item Name]),1))'}>}

Units)

[NickBentley]

Tresesco

Thank you for your solution, works a treat!

[NickBentley]

Hi Tresesco,

Using the expression above I am changing the '1' to a '2' hoping to get the unit quantity for the second ranked item (in terms of sales value) - for some reason this is not getting the anticipated results?

[NickBentley]

The expression works as expected when only one 'Store Name' is selected but gives incorrect results when all/multiple stores are selected. I beleive I have to lock the store name into the expression some how but not sure on how to do this.

Thanks 

[tresesco]

In that case, you probably have to remove set analysis section and use aggr() instead. Try to prepare a sample app and explain the expected output in that context, that would help us understand the issue better and propose a feasible solution.

[NickBentley82]

Thanks for your reply Tresesco. 

I am attempting to created a straight table with the dimension as Store Name. 

Expression 1 : Total Sales 

Expression 2:  #1 Selling Item (Name)

Expression 3:  #1 Selling Item (Sales Value)

Expression 4: Units sold of  #1 Selling Item (Units Sold)

Expression 5:  #2 Selling Item (Name)

Expression 6:  #2 Selling Item (Sales Value)

Expression 7: Units sold of  #2 Selling Item (Units Sold)

So far the #1 Selling Item Name and #1 Selling Item Sales Value is working correctly.  The issue is the units sold is not adding up correctly.

I am after an expression for each store that identifies the top selling item by value and the number of units sold for that item.  I would also like to select individual weeks as well as a series of weeks (i.e. weeks 1 - 3) at certain times. 

All suggestions are greatly appreciated 😊