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

Wed, 22 May 2013 09:15:45 GMT

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
Will	300	75
Will	100	50
Chris	50	20
Chris	500	100
Adam	1000	120
Adam	200	40
Adam	200	60
Adam	500	70

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

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

Wed, 22 May 2013 09:52:40 GMT

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"):

Customer	Sum of Sales	Avg Margin
Will	600	0.29
Chris	550	0.22
Adam	1900	0.15

Thanks,

Jens

topic From Excel-logic to QV-logic: Distinct aggregation & scatterplot in QlikView

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

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