value per max dimension in a category

[#1]

Hi All,

I need some help.

I have the following table in front end:

Key     Product      Month          Value

1          A               Jan               5

2          A               Jan               10

3          A               Feb               20

4          A               Feb               27

I need to get the following table as a result:

Month     Result

Jan          10

Feb          27

So I need to get as a result the Value per the max Key per month.

If I use sum({< Key = {"$(=max(Key, Month))"}>} Value) I only get this table as a result:

Month      Result

Jan               0

Feb               27

as it somehow calculates total max Key only, not per month.

Can you suggest the function I should use?

Thanks a lot.

[sunny_talwar]

May be this:

FirstSortedValue(Value, -Key)

[shree909]

Try this

sum(Aggr(max(Value),Month))

[johnw]

Edit: Oops, misread, never mind.

[#5]

This function gives me the max Values and not the Values per maximum Key.

So if I change my example:

Key     Product      Month          Value

1          A               Jan               10

2          A               Jan               5

3          A               Feb               27

4          A               Feb               20

I should get 5 and 20, but with this function it will be 10 and 27.

[sunny_talwar]

For getting 5 and 20, you can try this:

Dimension:

Month

Expression:

FirstSortedValue(Value, Key)

[vikramv]

See the attached....

[#8]

This works well.

However, how to do now assuming that I have more Products (A, B, C) and I need to sum all those cases of Value per max Key in month?

Sum(FirstSortedValue(Value, -Key)) doesnt work.

Thanks

[tyagishaila · Accepted solution]

Dimension: Month

Expression: FirstSortedValue(Value ,-Key)

[sunny_talwar]

May be this:

Sum(Aggr(FirstSortedValue(Value, -Key), month, Product))

I used this sample data:

Table:

Load Key,

  Product,

  Month(Date#(month, 'MMM')) as month,

  Value

Inline [

Key, Product, month, Value

1, A, Jan, 5

2, A, Jan, 10

3, A, Feb, 20

4, A, Feb, 27

5, B, Jan, 10

6, B, Jan, 9

7, B, Feb, 10

8, B, Feb, 20

9, C, Jan, 32

10, C, Jan, 123

11, C, Feb, 12

12, C, Feb, 127

];

Output:

Dimension

month

Expression

Sum(Aggr(FirstSortedValue(Value, -Key), month, Product))