Aggregation in pivot chart: summing parts on different levels

[#1]

Hi,

Please have a look at the attachement (QV used: QV9SR4)

My goal: to assign each date of sales in order.

eg:

     - Product AB1234, sold on 2/1/2011 should should be reconned as the first sale made on this product.

     - Product CD2468, sold on 4/4/2011 should be reconned as the third sale made on this product.

     - and so on: for each date there should be one "number" known.

Eventually i would like to be able to sum all "first" sales of all products (this means AB1234-2/1/2011 + AB6789-1/1/2011 + CD2468-1/1/2011 + CD4567-4/4/2011 equals to 4569 + 254789 + 2578 + 41446) but also all second sales per product and so on.

Any comments would be highly appreciated.

Kind regards

Ramon

[swuehl]

Ramon,

You just need to assign a SalesOrder ID in your load script:

Result:

LOAD *,

autonumber(recno(),Product) as SalesOrder

Resident Sheet1$ order by Product, DateSales;

Now every order of holds a number, per product.

Summing up the Amounts is per order number is easy then, in a chart grouped by SalesOrder dimension or

using

=sum({<SalesOrder = {1}>} InvoiceSales)

e.g. in a textbox to sum only the first sales orders.

Please have a look at attached sample.

[#3]

Fantastic, thank you!

Just one additional question: is there a possibility to get (more or less) the same result, but whitout using a script? If this can be done using expressions, I would like to know how.

Thank you in advance!

[#4]

Just to make clear why I would like to have the result using expressions: using the script is not officially part of my possibilities, so I cannot make any changes in the script without collaborating with the IT department.

Hopefully this problem can be solved using expressions

[swuehl]

You can do it in a chart object also, the exact syntax might depend on your data model and in what context you are using the expression.

Assuming that you have only 1 InvoiceSales per SalesDate, you can use something like:

=sum(aggr( 

firstsortedvalue(InvoiceSales, DateSales, 1 ),Product

))

to sum the InvoiceSale for the first time the Product was sold over all Products (the magic number 1 is indicating first sales, change to 2,3,4 if you want to query second, third, fourth).

See also attached.

[#6]

I tried this solution, and it works! Great!

One additional question: is it possible to use and expression on the place where is InvoiceSales? I tried, but did not succeed. In my real chart I have to calculate the InvoiceSales, so this is not part of my raw date.

[swuehl · Accepted solution]

You can also try using something like this, instead of sum(InvoiceSales) you should be able to put in you expression to calculate InvoiceSales.

=sum(aggr(if(DateSales = min(total<Product> DateSales), sum(InvoiceSales) ),Product,DateSales))

I am assuming that your calculation of InvoiceSales is somewhat trivial, like a simple sum. If not, this might complicate things a bit more.

[#8]

swuehl,

First of all, I would like to thank you for all your efforts replying to my questions.

For now, my questions ahve been answered perfectly. Thank you!