Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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!

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Select the Company with Max sales

Or this:

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

View solution in original post

6 Replies
Highlighted

Re: Select the Company with Max sales

May be this:

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

Highlighted

Re: Select the Company with Max sales

Or this:

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

View solution in original post

Highlighted

Re: Select the Company with Max sales

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 ...!

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)
Highlighted
Partner
Partner

Re: Select the Company with Max sales

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?

Highlighted

Re: Select the Company with Max sales

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

Highlighted
Partner
Partner

Re: Select the Company with Max sales

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

Thank you!