Sum sold quantity for different prices for a same product SKU

pedrohenriqueperna — Tue, 21 Mar 2023 17:16:49 GMT

Hello!

I've been asked to display the sold quantity of a product SKU for different price ranges this SKU have been sold.

The problem is that I need to display the different prices in columns, instead of lines. It means that for each Price column I need a corresponding column for the quantity, as shown in the picture below (expected result).

Each product has a same "reference code" that might hold different SKUs and a different "Original Price" (the first price registration).

The data model already holds a field record for each different price the product was sold, with that said I managed to show the different prices using FirstSortedValue by specifying the rank order (1 for Price 1 / 2 for Price 2 / etc.), but I can't make the calculation correspond to the Price X, it either sums everything or it shows only for 1 line if I add FirstSortedValue inside a set analysis.

I assume I have to work with Aggr and Sum. I tried different combinations, but I just can't get it to work.

The closest I got was this, but it only shows the correct value for a single line:

Sum({<NFTipoMovimento = {'Venda'}, PrecoEpoca = {"$(=(FirstSortedValue(Distinct PrecoEpoca, PrecoEpoca, 1)))"} >} [ProdutoQuantidade])

PS:

PrecoEpoca = Different price ranges the product has been sold

ProdutoQuantidade = Units solds

ProdutoReferencia = Reference code (1 level above SKU)

%ProdutoSKU = SKU code

PrecoOriginal = First price registration for that SKU

Any help is appreciated.

SOS

Re: Sum sold quantity for different prices for a same product SKU

Or — Wed, 22 Mar 2023 13:57:15 GMT

Perhaps I am misunderstanding your data structure, but if you have a list of sales lines, and each of them has a quantity and a price, wouldn't this be a pivot table with the Price field as the pivot dimension?

Re: Sum sold quantity for different prices for a same product SKU

pedrohenriqueperna — Wed, 22 Mar 2023 15:06:47 GMT

Hey!

Thank you for the reply.

It crossed my mind to use a pivot table, but I thought it wouldn't give me the expected result.

I've just played a bit with it and I think it might be the solution indeed.

Thanks for the tip!!!

topic Re: Sum sold quantity for different prices for a same product SKU in App Development

Sum sold quantity for different prices for a same product SKU

Re: Sum sold quantity for different prices for a same product SKU

Re: Sum sold quantity for different prices for a same product SKU