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?
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.
@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
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;
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.
