Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

value per max dimension in a category

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.

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Dimension: Month

Expression: FirstSortedValue(Value ,-Key)

View solution in original post

9 Replies
Highlighted

May be this:


FirstSortedValue(Value, -Key)

Highlighted
Partner
Partner

Try this

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

Highlighted
MVP
MVP

Edit: Oops, misread, never mind.

Highlighted
Not applicable

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.

Highlighted

For getting 5 and 20, you can try this:

Dimension:

Month

Expression:

FirstSortedValue(Value, Key)

Highlighted
Creator III
Creator III

See the attached....

Highlighted
Not applicable

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

Highlighted
Specialist
Specialist

Dimension: Month

Expression: FirstSortedValue(Value ,-Key)

View solution in original post

Highlighted

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:

Capture.PNG

Dimension

month

Expression

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