Add calculated dimension to display values of a max

[#1]

Hi All,

I have created a table as follows:

No     Name

5          D

5          B

5          C

5          A

5          E

3          C

3          B

3          A

4          A

4          B

4          C

4          D

4          E

4          F

2          D

2          A

2          C

2          B

I want to create a straight table with the dimension 'Name' and it should display only the 'Name' for the latest 'No'. The output should be:

Name

D

B

C

A

E

(that is only the values of the No 5)

Your help is much appreciated.

[MK_QSL]

Create a Straight Table

Use Calculated Dimension

=IF(Aggr(Max(No),Name)=Max(TOTAL No),Name)

Tick Suppress When Value is Null

Expression

Max(No)

or

1

or

whatever you want

Now go to Presentation tab and selection the expression and hide column

(Hide only, if you dont want to show it)

The same expression you can use in List Box Expression to show only ABCDE

=IF(Aggr(Max(No),Name)=Max(TOTAL No),Name)

If you want irrespective of selection, use below

=IF(Aggr(Max({<No = >}No),Name)=Max({<No = >}TOTAL No),Name)

[anbu1984]

Check this !

[#4]

I would actually go a different route, rather than a calculated demension I would create an indicator on load so you can always identify the latest record, you can then use a set in any expression to only see this:

Tmp_Table:

Load * Inline
[No, Name
5, D
5, B
5, C
5, A
5, E
3, C
3, B
3, A
4, A
4, B
4, C
4, D
4, E
4, F
2, D
2, A
2, C
2, B
];

left join(Tmp_Table)
load
max(No) as MaxNo
Resident Tmp_Table;

Table:
load
*,
if( No = MaxNo, 'Latest') AS Latest_Indicator
Resident Tmp_Table;

drop table Tmp_Table;

You can then use this expression in a straight table or any other expression rather than always repeating your calculated demension:

count({< Latest_Indicator = {'Latest'}>} Name)

p.s you can hide the expression if you don't want it visible on screen on the presentation tab