Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nguyenviethung
New Contributor III

How to get Inventory by week from days

Hi folks,

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

I got a inventory data table like this:

DATESTORESKUINVENTORY
1/1/2018Aa5
1/1/2018Ab2
1/1/2018Ac3
1/2/2018Aa6
1/2/2018Ab0
1/2/2018Ac5
1/3/2018Aa1
1/3/2018Ab2
1/3/2018Ac2
1/3/2018Ba2
1/3/2018Bb4
1/3/2018Bc8

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:

WeekSTORESKUINVENTORY
1Aa1
1Ab2
1Ac2
1Ba2
1Bb4
1Bc8

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.

8 Replies
ogautier62
Valued Contributor II

Re: How to get Inventory by week from days

Hi,

try something like this for inventory

if(date=max( date),inventory)

with(week, store, sku as dimension)

regards

nguyenviethung
New Contributor III

Re: How to get Inventory by week from days

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

Capture.JPG

Could you pls revise it?

Re: How to get Inventory by week from days

try this

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

Regards,

Regards,
Prashant Sangle

Re: How to get Inventory by week from days

or simply try

firstsortedvalue(INVENTORY,-DATE)

Regards

Regards,
Prashant Sangle
nguyenviethung
New Contributor III

Re: How to get Inventory by week from days

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

Capture.JPG

nguyenviethung
New Contributor III

Re: How to get Inventory by week from days

Can anyone helppppp meeee? Thankssss.

Re: How to get Inventory by week from days

How about this?

Capture.png

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
New Contributor III

Re: How to get Inventory by week from days

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