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 · Accepted solution]

Hello, Denis.

I think the easiest way is to create a new dimension, LastDate, in the script and check the option "Suppress When Value Is NULL" in the table. The code would be something 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,
  Date(Max(Date)) as LastDate
Resident WharehouseInventory
Group by Wharehouse;

An example app is attached.

Cheers.

[eduardo_sommer]

Create a straight table with wharehouse as dimension and firstsortedvalue(Inventory, -Date) as the expression. Please note the minus sign in the Date field, since we want the last week.

Eduardo

[jagan]

Hi Denis,

Try like this

Data:

LOAD

*,

If(RowNo() <> 1 AND Previous(WareHouse) <> WareHouse, 1, 0) AS MaxDateByWareshouse

FROM DataSource

ORDER BY WareHouse, Date;

Now do this in chart

Dimension : WareHouse

Expression : Sum({<MaxDateByWareshouse={1}>}Inventory)

Hope this helps you.

Regards,

Jagan.

[#5]

Thank you Bruno,

It works, but I would like to present the table without showing the date.

Is it a way to do that?

Thank you

[#6]

Hi Edoardo,

Thank you for your responce.

When I use the formula I am getting no results.

Should I add anything else to expression?

Thank you

[eduardo_sommer]

Hi,

Look at the attached app. It's very easy

Eduardo

[#8]

HI Bruno,

Please ignore my previous post.

I just need to hide the column in Presentation tab.

Thank you for your help.

[#9]

Hi Eduardo,

It works if you have only one dimensions - wharehouse, but if the table has more fields - as product type, product color etc the formula does not work properly.

I am not familiar with 'firstsortedvalue' function, but if it is possible to add the group by to it it would help.

Thanks  a lot

[eduardo_sommer]

Hi Denis

If you have more dimensions, besides Wharehouse, they will work as a 'group by'. If you want to get the inventory of a color, of a product, of certain type, just add these dimensions and the expression will take the last inventory of that color of that product of that product type.

The firstsortedvalue takes the first value in a group of records (be them in a loading script or in a chart- pivot or straight table, for example). In the case of the chart, the dimensions act as the group by in the load statement. When you want the last record, you put a minus sign in front of the order variable - kind of a lastsortedvalue. For this reason i put -Date in the function.

It looks too simple to be true, but it works very fine.

Eduardo