topic Re: Last Entered Inventory Number in QlikView

Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 06:47:49 GMT

Hi,

I have data set including following Columns

Transaction Date, Data Week, Qty

I want last data week total Qty

Thanks,

Vikas

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 06:54:31 GMT

Perhaps this?

Sum({<[Data Week] = {$(=Max([Data Week])-1)}>} Qty)

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 07:06:53 GMT

My Data set contains unique month and repeating Data Week as shown below

I want closing or last entered inventory of the month

Say Apr = 10962411.34

Months	DataWeek	Qty
Jan	1	12294609.88
Jan	2	11979212.28
Jan	3	12375512.48
Jan	4	12351222.22
Feb	1	12649753.37
Feb	2	12649753.37
Feb	3	12968125.98
Feb	4	13064135.69
Mar	1	13337458.61
Mar	2	13337458.61
Mar	3	0
Mar	4	0
Apr	1	11719474.23
Apr	2	10615512.64
Apr	3	10534975.27
Apr	4	10962411.34

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 07:10:16 GMT

This will deserve

Sum({<[DataWeek] = {$(=Max([DataWeek]))}, Months = {'$(=Date(Max(Month(Date#(Months))),'MMM'))'}>} Qty)

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 07:11:50 GMT

Or Change it to Months from Month name to Month number, That makes more clear like

Num(Month(Date#(Months))) as MonthNum

And, then expression should be this?

Sum({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>} Qty)

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 08:10:07 GMT

Sum({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>} Qty)

And

Sum({<[DataWeek] = {$(=Max([DataWeek]))}>} Qty)

both gives me same output

When I select a single month, Out put is correct, but when all my month filters are open, It gives me sum of all entries from 4th Data week of all months.

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 08:41:26 GMT

Where are you trying this? In text box / tabular report?

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 08:43:12 GMT

Text Box

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 08:44:56 GMT

I believe should work if it is Text box? Can you show image in straight table with same expression and same in text box, Please?

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 09:38:15 GMT

Its Giving Me below numbers

Months	DataWeek	Grand Total
Jan	4	89803.95
Feb	4	92030.71
Mar	4	78241.58
Apr	4	90184.88
May	4	88442.61
Jun	4	90278.12
Jul	4	95113.84
Aug	4	91170.62
Sep	4	91172.9
Oct	4	92571.38
Nov	4	90084.26
Grand Total		989094.85

Re: Last Entered Inventory Number

mdmukramali — Tue, 26 Dec 2017 09:42:17 GMT

Hi Vikas,

Anil solution is working fine,

have a look :

=Num(Sum({<MonthNum={'$(=Max(MonthNum))'},DataWeek={'$(=Max(DataWeek))'}>}Qty),'#,##0')

PFA,

Thanks,

Mukram.

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 09:43:49 GMT

May be this?

Max({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>}Aggr(Sum({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>} Qty)))

This will return as below in text box? 90084.26

Re: Last Entered Inventory Number

Anil_Babu_Samineni — Tue, 26 Dec 2017 09:48:13 GMT

My bad, Months and DateWeek should cover for Aggr()

Max({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>}Aggr(Sum({<[DataWeek] = {$(=Max([DataWeek]))}, MonthNum = {'$(=Max(MonthNum))'}>} Qty), Months, DataWeek))

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 09:53:50 GMT

Nope incorrect expression

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 10:00:50 GMT

I am attaching my data sheet and my App

Please check

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 10:04:03 GMT

I am attaching my data sheet and my App

Please check

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 10:16:46 GMT

Hi,

I am attaching my excel and app

Re: Last Entered Inventory Number

tresB — Tue, 26 Dec 2017 10:20:55 GMT

What is your expected output in the sample provided? For latest week's data try:

Sum({<[DataWeek] = {$(=Max({<[Transaction Date]={"$(=Max([Transaction Date]))"}>} DataWeek))}, Month = {$(=text(Month(Max([Transaction Date]))))}>} InvQty)

Re: Last Entered Inventory Number

vikas_nandanwar — Tue, 26 Dec 2017 10:22:53 GMT

90084.26

Re: Last Entered Inventory Number

tresB — Tue, 26 Dec 2017 10:23:51 GMT

Try my above expression.