Data based on different date variables?

[#1]

Hi All,

I have a simple question, but probably with a complicated answer.

I have a data table which contains simplified:

Customer - Order - Article - #Delivered - #Sales - #Returns - OrderDATE - SalesDATE

Now I get stock reports from the customers which contain the following:

Customer - Article - #Stockamount - StockDATE

Now I want to calculate their #Stockamount and our virtual stock amount (as a sum: #Delivered - #Sales - #Returns = stock) and compare those.

But now I only want to use their latest StockDATE (ie. sum({<StockDATE = {$(=max(StockDATE))}>} #Stockamount)) to calculate their stock.

Here I run into problem 1; It will only calculate based on the max StockDATE found, not per customer in the table., untill I select a customer.

The second problem I have is that I want to base the #Delivered, #Sales and #Returns on DATE, everything between first known date UNTILL the max StockDATE, And this is different for each customer as well.

Any thought or ideas?

[marcus_sommer]

Try for your first topic something like this:

sum(aggr(firstsortedvalue(#Stockamount, -StockDATE), Customer))

By your second topic I'm not sure if something similar could be applied. Quite often it's helpful to precalculate such things like the max(StockDATE) within the script like:

MaxStockDATE:

Load Customer, max(StockDATE) as MaxStockDATE Resident abc Group by Customer;

and use this field within your expressions.

- Marcus

[#3]

Thanks for your reply. The firstsortedvalue aggr doesnt give me any good results.

About the second part, I have added that to the script already. But How can I filter? It won't be linked to any of my dates whatsoever and I can't get it to work properly in my expressions. I will post an example asap. Reloading as we speak..