topic Re: Summing Firstsortedvalue in QlikView

Summing Firstsortedvalue

NickBentley — Wed, 20 Jan 2021 00:17:58 GMT

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!

Re: Summing Firstsortedvalue

edwin — Wed, 20 Jan 2021 19:46:37 GMT

can you attach your QVW?

Re: Summing Firstsortedvalue

NickBentley — Wed, 20 Jan 2021 21:57:38 GMT

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

Re: Summing Firstsortedvalue

NickBentley — Thu, 21 Jan 2021 06:04:31 GMT

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.

Re: Summing Firstsortedvalue

tresB — Thu, 21 Jan 2021 06:13:49 GMT

Give a try like:

Sum(

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

Units)

Re: Summing Firstsortedvalue

NickBentley — Thu, 21 Jan 2021 06:19:17 GMT

Tresesco

Thank you for your solution, works a treat!

Re: Summing Firstsortedvalue

NickBentley — Thu, 21 Jan 2021 23:59:37 GMT

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?

Re: Summing Firstsortedvalue

NickBentley — Fri, 22 Jan 2021 00:56:28 GMT

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

Re: Summing Firstsortedvalue

tresB — Fri, 22 Jan 2021 05:22:04 GMT

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.

Re: Summing Firstsortedvalue

NickBentley82 — Fri, 22 Jan 2021 06:23:43 GMT

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 😊