Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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'
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'
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' )
use the "firstsortedvalue()" function: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/BasicAggregationFun...
FirstSortedValue(State,aggr(-sum([Sales]),State))
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'
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..
max(aggr(sum(Sales),State)) for your max State Sales