Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Customized message in text box

Hi All,

I am trying to create a customized message in a text box based on the highest sales value.

I have the data as follows:

State          Sales 2018

A                    300

B                    400

C                    350

D                    150

E                    600

F                    250

Based on the maximum sales of a particular state I would like the text box to display the following message:

"Well done (State name)" on achieving (sales value) sales in 2018."

The state name with the maximum sales (in this case State E) should be shown in the message along with its value (i.e. 600).

Please help..

1 Solution

Accepted Solutions
manas_bn
Creator
Creator

2 Options for you -

Use this in the load script:

Table:

Load * Inline [

State, Sales2018

A, 300

B, 400

C, 350

D, 150

E, 600

F, 250];

// Create a table to store only the state with max sales and the max sales

MaxSalesTable:

First 1 Load State as BestState,Sales2018 as MaxSales Resident Table order by Sales2018 desc;

Use a text box with this expression: ='Well Done '&BestState&' on achieving '&MaxSales&' sales in 2018'


Use this in the front end in a text box

'Well Done '&FirstSortedValue(State,aggr(-sum([Sales2018]),State))&' on achieving '&FirstSortedValue(Sales2018,aggr(-sum([Sales2018]),State))&' sales in 2018'


View solution in original post

6 Replies
MK9885
Master II
Master II

Maybe try

=

if(GetSelectedCount(State)=0,

'Well done' & chr(10)& State& chr(10)&' on achieving' & chr(10)& Max([Sales 2018]) & chr(10)&'Sales in 2018' ,

'Well done' & chr(10)& State& chr(10)&' on achieving' & chr(10)& Max([Sales 2018])& chr(10)& 'Sales in 2018' )

Either way whatever you select it will show that message

You can remove if condition and just use below expression

'Well done' & chr(10)& State& chr(10)&' on achieving' & chr(10)& Max([Sales 2018])& chr(10)& 'Sales in 2018'

balabhaskarqlik

May be:

if(GetFieldselections([Sales 2018])=max([Sales 2018]),

'Well done' & chr(10)& State& chr(10)&' on achieving' & chr(10)& Max([Sales 2018]) & chr(10)&'Sales in 2018' ,

'Ok Sales in 2018' )

mikaelsc
Specialist
Specialist

use the "firstsortedvalue()" function: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/BasicAggregationFun...

FirstSortedValue(State,aggr(-sum([Sales]),State))

manas_bn
Creator
Creator

2 Options for you -

Use this in the load script:

Table:

Load * Inline [

State, Sales2018

A, 300

B, 400

C, 350

D, 150

E, 600

F, 250];

// Create a table to store only the state with max sales and the max sales

MaxSalesTable:

First 1 Load State as BestState,Sales2018 as MaxSales Resident Table order by Sales2018 desc;

Use a text box with this expression: ='Well Done '&BestState&' on achieving '&MaxSales&' sales in 2018'


Use this in the front end in a text box

'Well Done '&FirstSortedValue(State,aggr(-sum([Sales2018]),State))&' on achieving '&FirstSortedValue(Sales2018,aggr(-sum([Sales2018]),State))&' sales in 2018'


Anonymous
Not applicable
Author

Thanks guys for your responses.

What if my sales column is a calculated field?

What's happening is that when I am applying the Max() function to the calculated field, it is not splitting up the value as per the states and selecting the max sales, it's giving me just one value which is a sum total of all the sales.

Please suggest how to get around this.

Thanks..

mikaelsc
Specialist
Specialist

max(aggr(sum(Sales),State)) for your max State Sales

Aggr() function