How to calculate Inventory of max date

[#1]

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

[#2]

My qvw file ple see the attachment.

[Sokkorn]

Hi Zhou,

Create new expression with FirstSortedValue(Inv, -Dt)

Regards,

Sokkorn

[#4]

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

[Sokkorn]

Zhou,

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

Regards,

Sokkorn

[#6]

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.

[Anonymous]

In the script:

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

Warning: There is probably a better solution.

[jagan · Accepted solution]

Hi,

Use this expression

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

Regards,

Jagan.

[#9]

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.

[#10]

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