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: 
crystles
Partner - Creator III
Partner - Creator III

Select the Company with Max sales

I am trying to create a formula for a SVG map. I have a list of three companies I am comparing. I need to have the map color the different states (USA) depending on which company has the most sales in that state.

Ex

Company A =Blue

Company B = Green

Company C = Orange

So if Company A has the highest sales in Georgia, then the entire state of Georgia will be Blue.

I have the basis for the equation, I just need the piece that defines the max for each state.

Example of formula

If( *code to find Max Company by sales* = CompanyA, RBG(Blue)

     If( *code to find Max Company by sales* = CompanyB, RBG(Green)

          If( *code to find Max Company by sales* = CompanyC, RBG(Orange) ))

Any ideas or suggestions would be greatly appreciated! Thanks!

1 Solution

Accepted Solutions
sunny_talwar

Or this:

FirstSortedValue(Company, - Aggr(Sum(Sales), Company, State))

View solution in original post

6 Replies
sunny_talwar

May be this:

FirstSortedValue(Company, - Aggr(Sum(Sales), Company))

sunny_talwar

Or this:

FirstSortedValue(Company, - Aggr(Sum(Sales), Company, State))

Anil_Babu_Samineni

Perhaps This

Should write this BG color under +

If( CompanyA = {'$(=Max(Sum(Sales)))'}, Blue()

     If( CompanyB = {'$(=Max(Sum(Sales)))'}, Green()

          If( CompanyC = {'$(=Max(Sum(Sales)))'}, Orange() ))


OR

If( FirstSortedValue(CompanyA,Sum(Sales)), Blue()

     If( FirstSortedValue(CompanyB,Sum(Sales)), Green()

          If( FirstSortedValue(CompanyB,Sum(Sales)), Orange() ))


Hope This Helps ...!

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
crystles
Partner - Creator III
Partner - Creator III
Author

Thank you Sunny T for the quick response. I have been trying to incorporate this into my SVG map chart.

I have this as the Measure = Max(aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE))

and the Color Expression is

=if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'BB', RGB(26,71,135),
    
if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'TH', RGB(135,37,26),
        
if(FirstSortedValue(MFR_CODE, - aggr(Sum(DISTINCT MarketShare_ALL), STATE_REGISTRATION, MFR_CODE)) = 'IC', RGB(237,231,45))))

I thought it was working, but now the entire map is black? I don't have the disabled or the Base color as black so this has to be something the color expression is doing? I'm wondering if it is trying to color each state as each of the 3 three colors at the same time and it's turning them black? I've never had this error before?

sunny_talwar

I have never used the SVG maps before so not really sure what it might be doing. May be someone with experience is able to provide better directions

crystles
Partner - Creator III
Partner - Creator III
Author

I'm not sure what I was doing before but the formula works perfectly now.

Thank you!