topic Re: How to calculate Inventory of max date in QlikView

How to calculate Inventory of max date

Thu, 23 Jan 2014 02:35:15 GMT

Hi all,

I have a table like this:

Month Date Inv

1 25 101

1 27 99

1 29 78

2 6 19

2 10 6

2 21 10

I want to calculate the Inv of the Max date in each month. The result table i want is like this :

Month Inventory

1 78

2 10

78 is the inventory of 1-29 and 10 is the inventoy of 2-21.

Pls help me, thanks.

zhou

Re: How to calculate Inventory of max date

Thu, 23 Jan 2014 02:38:02 GMT

My qvw file ple see the attachment.

Re: How to calculate Inventory of max date

Sokkorn — Thu, 23 Jan 2014 03:28:00 GMT

Hi Zhou,

Create new expression with FirstSortedValue(Inv, -Dt)

Regards,

Sokkorn

Re: How to calculate Inventory of max date

Thu, 23 Jan 2014 03:40:23 GMT

I worked only when there is 1 records of the max date.

Re: How to calculate Inventory of max date

Sokkorn — Thu, 23 Jan 2014 04:16:50 GMT

Zhou,

What is the issue you face right now? I totally don't understand your reply above.

Regards,

Sokkorn

Re: Re: How to calculate Inventory of max date

Thu, 23 Jan 2014 04:37:27 GMT

What i mean is : If there are two records at date 1-29, then your expression 'firstsortedvalue(Inv,-Dt)' will return 'Null'.

You can refer my qvw file in the attachment.

Thanks.

Re: How to calculate Inventory of max date

Anonymous — Thu, 23 Jan 2014 05:03:23 GMT

In the script:

If(Month <> Peek(Month,-1),Inv,'') as MaxInv

Warning: There is probably a better solution.

Re: How to calculate Inventory of max date

jagan — Thu, 23 Jan 2014 05:03:35 GMT

Hi,

Use this expression

=Sum(Aggr(If(Dt =Date(Max(TOTAL<Month> Dt)), Sum(Inv)), Dt, Month))

Regards,

Jagan.

Re: How to calculate Inventory of max date

Thu, 23 Jan 2014 05:38:26 GMT

Find the

Hi,

Use this

FirstSortedValue(aggr(max(Inv),Dt),Dt)

If duplicate values, it will return max,

If need min value USe

FirstSortedValue(aggr(min(Inv),Dt),Dt)

Find the attached file.

Re: Re: How to calculate Inventory of max date

Thu, 23 Jan 2014 05:59:34 GMT

You are right! You solved my problem. Thank you!