Working on Last Value -Aggregation

[former_member596753]

Dear All ,

I have following scenario .

Division     Struc                Month    Value

DA1         STR1                 Jan 14     200

DA1         STR1               FEB 14    300

DA1         STR1              March 14    100

Now i want to show

DA1       STR1      100

How can we do that

[former_member190895 · Accepted solution]

[former_member190895 · Accepted solution]

Max Month==Max([Month]) In Block

In Seconds Block  =[Value] Where ([Month]=[Max Month])

@Arijit, I dont think we need to format date in this case.

[arijit_das · Accepted solution]

Date object should also work in Max function. Formatting it as yyyyMM is optional.

[arijit_das]

First you need to have a fixed Month format so that we can create a key out of it. If you already have a key in yyyyMM format, you can directly use that.

I assume you have the month in Mmm yyyy format.

Create following variables:

v_MthKey =FormatDate(ToDate([Month];"Mmm yy");"yyyyMM")

v_Value =([Value] Where ([v_MthKey]=Max([v_MthKey]) In Block))

Use v_Value in your table.