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: 
EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III

Use sum(sales) inside max() function in script

Hi everyone, I have been asked to make 2 KPIs which show calculated dimension from the script. I have one table called "Principal" with the dimensions:

Company

Office

Type of Insurance

Sales

What I am trying to show in the KPI is the Type of Insurance with most sales according to a company nad an office. So my first thought was to create a table called maxInsurance:

MaxInsurance:

load:

    Company,

    Office,

    max(sum(sales))

Resident Principal Group By Company,Office;

With the intention of getting the max value of sales, and then I would have to somehow pair that data with Type Of Insurance; but I can't get that maxValue because the expression is wrong. Any suggestions?

7 Replies
tresesco
MVP
MVP

What if you just use max(sales) ? Because if you use sum(sales), you would get single sales figure for every combination of companies and offices - then there would be no meaning of getting max out of a single record, right? I am not sure if my understanding is correct. It's a question of data grain.

asinha1991
Creator III
Creator III

maybe try this.

A:

Load

Company,

Office,

Type of Insurance,

sum(Sales) as Sales

Resident Principal Group by

Company,

Office,

Type of Insurance

;

 

inner join(A)

load 

Company,

Office,

max(Sales) as Sales Resident A Group by 

Company,

Office;

 

EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III
Author

@tresesco  I think I do need to have one unique value for every pair of company-office, becasue I want to get the type of insurance that has made the max amount of sales according to a company and an office

So for example if I had the table:

Company|Office|Type Insurance|Sales

A                        1                 A                       10

A                        1                 B                        9

A                        1                 C                       10

A                        1                 C                       10

A                        2                 B                       5

A                        2                 C                       30

A                        2                 A                       10

A                        2                 A                       30

 

the result for my KPI if I selected the company A and office 2 should be Type Of insurance  A as the max sum of sales of company A and office 2  is 40 --> Type of insurance A

I htink I am explaining myself very badly

tresesco
MVP
MVP

May be using two stages of aggregation and firstsortedvalue(), like this:

Input:
Load 
	Company,
	Office,
	"Type Insurance",
	Sum(Sales) as Sales
Group By 	Company,Office,"Type Insurance";	
	
Load * Inline [
Company Office "Type Insurance" Sales

A                        1                 A                       10

A                        1                 B                        9

A                        1                 C                       10

A                        1                 C                       10

A                        2                 B                       5

A                        2                 C                       30

A                        2                 A                       10

A                        2                 A                       30] (delimiter is spaces);

Output:
Load
	Company,
	Office,
	FirstSortedValue("Type Insurance",-Sales) As [Type of Insurance],
	Max(Sales) as MaxSales
Resident Input Group By Company, Office;

Drop Table Input;	

 

EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III
Author

@tresesco that would be great except for the fact that I have been told not to use FirstSortedValue 😞

EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III
Author

inspired by what @asinha1991  proposed I have been able to get the max value of sum(sales), now i just need to pair the value with the type of insurance without using FirstSortedValue.

 

EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III
Author

Ok I think I finally got it! thanks for everything