Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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.
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
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
Hi,
Look at the attached app. It's very easy
Eduardo
HI Bruno,
Please ignore my previous post.
I just need to hide the column in Presentation tab.
Thank you for your help.
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
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