Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Maximum of measure per unique dimension and month

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.   

Ajax Co.Ben1/15/2017Customer11000
Ajax Co.Ben1/17/2017Customer1900
Ajax Co.Mark1/2/2017Customer10900
Poseidon Inc.Alfred5/5/2017Customer11100
Poseidon Inc.Jonas1/11/2017Customer121400
Poseidon Inc.Jonas1/11/2017Customer121800
Poseidon Inc.Jonas3/5/2017Customer12800
Poseidon Inc.Jonas3/27/2017Customer131200
Poseidon Inc.Jonas3/27/2017Customer13300

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.


0 Replies