Create field for column based on majority value

[stelozoya]

Hi Everyone, I am new to Qlik Sense and don't have a whole ton of developer background. Any help you guys have I will greatly appreciate it.

I have three columns that I am trying to use to create one additional column that gives me the location with the Main Channel based on the jobs.  I have tried doing a sum if on channel and try to bring in the one with the majority sales. I have looked at other questions posted that have answers and have tried with aggregates and max with no luck. 

This is an example with two channels but in my real table I have 5 different channels. Thank you all!

I want my output to be like below:

Location	Main Channel
1	Original
2	Original
3	Second

[stevejoyce]

Are you basing this on the sum of Jobs per Location?  I'm not sure why Location 2 = original and Location 1 = original.

[stelozoya]

One location can have multiple Channels, each channel has a number of jobs. For example location 2 has both channels but channel = original has 5 jobs which would make it the main channel. Since the second channel only has 2.

Basically it is Original=5 vs Second= 2, Majority for location two is original. I hope that makes a little more sense. I am able to do the sums but I can't figure out how to do the calculation on the back and only have the output being the channel. 

[stevejoyce]

That makes sense for Location 2.  How is Location 1 original?

[stelozoya]

You are right, it is incorrect. The correct output would be second. 

[stevejoyce · Accepted solution]

You should be able to use firstsortedvalue to get to this.  

Chart expression, something like:

firstsortedvalue(total <Location> Channel, -(aggr(sum(Jobs), Location, Channel)))

if it can be in script:

data:
load * inline [
Location, Channel, Jobs
1, Original, 5
1, Original, 5
1, Second, 11
2, Second, 2
2, Original, 5
3, Second, 1
3, Second, 2
]
;

sum_jobs:
load
Location
,Channel
,sum(Jobs) as sumJobs
resident data
group by Location, Channel;

left join (data)
maxjobs:
load
Location
,Firstsortedvalue(Channel, -sumJobs) as [Main Channel]
resident sum_jobs
group by Location;

drop table sum_jobs;

[stelozoya]

Thank you so so so much! This worked perfectly, I had been trying to figure it out for two weeks. I appreciate it so much!