Maximum of measure per unique dimension and month - Qlik Community

hbeukenboom · ‎2017-07-24

Hi Community Experts,

I have been using QlikView for a few month now and am new to Qlik Community. I have a question which I have been trying to solve for several days now. Hope you can help.

I have the following QlikView document containing a data model with a Calendar Table and a Fact table containing Sales Amounts per Company, Agent, Date, and Customer(ID). See first rows below.

Company	Agent	Date	Customer	Amount
Ajax Co.	Ben	1/15/2017	Customer1	1000
Ajax Co.	Ben	1/17/2017	Customer1	900
Ajax Co.	Mark	1/2/2017	Customer10	900
Poseidon Inc.	Alfred	5/5/2017	Customer11	100
Poseidon Inc.	Jonas	1/11/2017	Customer12	1400
Poseidon Inc.	Jonas	1/11/2017	Customer12	1800
Poseidon Inc.	Jonas	3/5/2017	Customer12	800
Poseidon Inc.	Jonas	3/27/2017	Customer13	1200
Poseidon Inc.	Jonas	3/27/2017	Customer13	300

I have created the following View Pivot Table based on this Fact Table

with the following expressions:

Sales: =sum(Amount)

#Customers: = count(distinct(Customer)) with a count of the distinct customers

SalesPerCustomerPerMonth: =avg(aggr(sum(Amount), YYYYMM, Customer)) containing the sales per month per unique customer

I want to find the highest total sales amount per Company and Agent and the Agent that has total sales amount and store that in a text box.

This I managed with the expressions:

=FirstSortedValue(Agent, -aggr(sum(Amount), Company, Agent)) to find the corresponding agent

=Num(Max(aggr(sum(Amount), Company, Agent)), '$ #,##0.00') representing the highest sales amount

Which looking at the pivot table is the correct result.

My problem starts when using a similar approach I try to find the Highest Sales per (unique) Customer per Month and the corresponding Agent. According to the pivot table the Agent is Carmen who has the highest SalesPerCustomerPerMonth of 1,233.33.

What expressions should I use to get this result?

The QVW is attached.

Thanks in advance for your help.

Herbert