Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Display '$' symbol to only highest sales amount on condition.

Hello all,

The possible output should be the following.

Name

Sort order

Sales Amount

Symbol

Mike

3

13000

$

Jim

3

400

Robert

3

200

James

4

16000

Robert

4

700

Jack

5

14000

Kevin

5

1000

Description of the issue:

The data contains only following fields.

1.      Name

2.      Sort Order

3.      Sales Amount

We want to add a dollar symbol “$” sign at the 13,000 as it is the highest amount in the lowest sort order #


PFA for sample database.


Any help would be appreciated.


Thanks in Advance!


The task can be shown in with straight table or table box.



BR

Susvith

1 Solution

Accepted Solutions
sunny_talwar

You can try this expression in the straight table:

=If(SortOrder = Min(TOTAL SortOrder) and [Sales Amount] = Max(TOTAL <SortOrder>[Sales Amount]), '$')

Dimension

Name

SortOrder

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

You can try this expression in the straight table:

=If(SortOrder = Min(TOTAL SortOrder) and [Sales Amount] = Max(TOTAL <SortOrder>[Sales Amount]), '$')

Dimension

Name

SortOrder

Capture.PNG

maxgro
MVP
MVP

try this in a straight table with Name and SortOrder as dimensions

if(rank(total sum({$ <SortOrder={"$(=min(SortOrder))"}>} [Sales Amount]))=1, '$', '')

1.png

sunny_talwar

Or this for the script to display it in a table box object:

Table:

LOAD Name,

    SortOrder,

    [Sales Amount]

FROM

[Book1 (8).xlsx]

(ooxml, embedded labels, table is Sheet2);

Left Join (Table)

LOAD *

Where RecNo() = 1;

LOAD SortOrder,

  Max([Sales Amount]) as [Sales Amount],

  '$' as Symbol

Resident Table

Group By SortOrder

Order By SortOrder;


Capture.PNG


sunny_talwar

A simpler script would be this:

Table:

LOAD Name,

    SortOrder,

    [Sales Amount]

FROM

[Book1 (8).xlsx]

(ooxml, embedded labels, table is Sheet2);

FinalTable:

LOAD *,

  If(RowNo() = 1, '$') as Symbol

Resident Table

Order By SortOrder, [Sales Amount] desc;

DROP Table Table;