Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
