Sum by Max Date

[#1]

Hello,

I have a table that contains warehouse, week and Inventory Units.

I would like to build a table that will dispaly the inventory the warehouse and inventory for a last week.

Something Like that:

Raw data:

Wharehouse      Date           Inventory

A                          1                   500

A                          2                   450

B                          1                    300

B                          2                     250

Table:

Wharehouse     Inventory

A                             450

B                            250

Please Help

Thank you

[Anonymous]

You're welcome, Denis.

Even though you said to ignore your previous post, let me add this. I thought you'd want to display the date so I used the field LastDate. But you don't have to. You could create a flag, like this:

WharehouseInventory:  
Load * Inline [  
    Wharehouse, Date, Inventory  
    A, '01/01/2014', 500  
    A, '02/02/2014', 450  
    B, '01/01/2014', 300  
    B, '02/02/2014', 250  
];  

Left Join (WharehouseInventory) Load  
  Wharehouse,  
  Date(Max(Date)) as Date,  
  1 as FlagLastInventory  
Resident WharehouseInventory  
Group by Wharehouse;

Then use the flag in your expression:

Sum({$<FlagLastInventory = {1}>} Iventory)

Or you can create both fields and use each one where you think is best.

Bruno.

[#3]

Thank you Bruno