Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kaustabh_mitra
New Contributor II

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
Contributor

Re: Customized message in text box

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'


6 Replies
Khan_Mohammed
Honored Contributor II

Re: Customized message in text box

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
Honored Contributor

Re: Customized message in text box

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
Contributor III

Re: Customized message in text box

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
Contributor

Re: Customized message in text box

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'


kaustabh_mitra
New Contributor II

Re: Customized message in text box

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
Contributor III

Re: Customized message in text box

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

Aggr() function

Community Browser