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: 
Not applicable

From Excel-logic to QV-logic: Distinct aggregation & scatterplot

Dear all QlikViewers,

I'm desperately trying to take the final steps from Excel to Qlikview. However, I always seems to get locked in small silly complications. The following is one of these.

I have a dataset (xl-format) and I wish to do some calculations with this or manipulations if you like

The data is all in one table (let's call it "main table"), and the first step - at least that's what I think - is understanding how I create new "calculated/manipulated" tables from the main table. Let me give you an example of the data, I'm looking at:

Customer
Sales
Profit

Will

200

50
Will30075
Will10050
Chris5020
Chris500100
Adam1000120
Adam20040
Adam20060
Adam50070

From this I wish to create a table showing the Average Margin per Customer along with the Sum of Sales per Customer.

The calculations I use are:

  • Margin = Sales/Profit
  • Average Margin = avg(Margin)
  • Sum of Sales = sum(Sales)

Now, to do this on the basis of "per customer", I use the following formulas:

  • Sum of Sales per Customer = aggr(sum(Sales),Customer)
  • Average Margin per Customer = aggr(avg(Margin),Customer)

And this actually yields the results I need. However, I am not able to create a new table with these results... In addition, I wish to plot Sum of Sales per Customer vs. Average Margin per Customer as a scatterplot - and for some reason this won't work.

I wish, I was able to track down the information needed to solve my issues - I'm confident that the input I need is already available some where out there.. My main problem is the fact that being new to QlikView, my QV-vocabulary lacks diversity and I have no clue as to what I should search for.

All inputs are happily accepted.

Great day everyone,

Jens

1 Reply
Not applicable
Author

Just to clearify, here's an example of the table I'd like to create (such that I can see it in the table viewer - and the link is "Customer"):

CustomerSum of SalesAvg Margin

Will

6000.29
Chris5500.22
Adam19000.15

Thanks,

Jens