How to get Inventory by week from days

[nguyenviethung]

Hi folks,

Thanks for helping me in previous topics. Now i have another concern:

I got a inventory data table like this:

DATE	STORE	SKU	INVENTORY
1/1/2018	A	a	5
1/1/2018	A	b	2
1/1/2018	A	c	3
1/2/2018	A	a	6
1/2/2018	A	b	0
1/2/2018	A	c	5
1/3/2018	A	a	1
1/3/2018	A	b	2
1/3/2018	A	c	2
1/3/2018	B	a	2
1/3/2018	B	b	4
1/3/2018	B	c	8

Note: Some store have daily inventory data, some just have 1 day/week.

Now what i want is: Measuring inventory by SKU by Store by Week; like this:

Week	STORE	SKU	INVENTORY
1	A	a	1
1	A	b	2
1	A	c	2
1	B	a	2
1	B	b	4
1	B	c	8

which means:

• if stores have only 1 day data in a week -> that day would be the inventory for that whole week
• if stores have many day data in a week -> the latest day inventory will be that week final inventory

Can anyone tell me how to write the expression or load in script?

Thank you all.

[ogautier62]

Hi,

try something like this for inventory

if(date=max( date),inventory)

with(week, store, sku as dimension)

regards

[nguyenviethung]

Yours sounds good but I dont know why it does not work, bro:

Could you pls revise it?

[PrashantSangle]

try this

sum({<DATE={"$(=max(DATE))"}>}INVENTORY)

Regards,

[PrashantSangle]

or simply try

firstsortedvalue(INVENTORY,-DATE)

Regards

[nguyenviethung]

Bro, sounds good but none of the above works; could you pls double check it:

[nguyenviethung]

Can anyone helppppp meeee? Thankssss.

[Anil_Babu_Samineni]

How about this?

302873:

LOAD * Inline [

DATE, STORE, SKU, INVENTORY

1/1/2018, A, a, 5

1/1/2018, A, b, 2

1/1/2018, A, c, 3

1/2/2018, A, a, 6

1/2/2018, A, b, 0

1/2/2018, A, c, 5

1/3/2018, A, a, 1

1/3/2018, A, b, 2

1/3/2018, A, c, 2

1/3/2018, B, a, 2

1/3/2018, B, b, 4

1/3/2018, B, c, 8

];

Right Join(302873)

LOAD Max(DATE) as DATE

Resident 302873;

[nguyenviethung]

Thank you bro; but your solution seems to load only latest date data. What I want is weekly inventory; including history data.