
Re: How to generate a Whale Curve and Cumulative Profit graph from unsorted raw sales data
Oleg Troyansky Feb 9, 2015 1:41 PM (in response to William Choo)Hi William.
I wish my new book QlikView Your Business was already out!!! It's coming out in April. In the book, I am giving very detailed stepbystep instructions on building the Whale Tail chart. You'll get to build one as part of the tutorial.
Assuming that you don't want to wait till April to buy the book, let me give you some abbreviated instructions here:
Chart Type: Scatter Chart.
Dimension: Customer
XAxis Expression: Cumulative Sales = Sum(Sales), using Full Accumulation.
YAxis Expression: Cumulative Profit Amount = sum(Profit), using Full Accumulation
Sorting Expression: Profit Margin Percent, Descending = sum(Profit)/Sum(Sales)
In order to enhance the chart, you can add four Reference Lines:
1. Actual Total Sales
2. Sales for the products with positive Profit margins (Sales to reach maximum profit)
3. Actual Profit
4. Maximum possible Profit (sum of all the positive profits)
These calculations are a bit tricky, and I'll leave these extra details for you to discover in the book. Check it out, it's already listed on Amazon with a discounted preorder price.
cheers,
Oleg Troyansky
www.masterssummit.com  take your QlikView skills to the next level!

Re: How to generate a Whale Curve and Cumulative Profit graph from unsorted raw sales data
William Choo Feb 9, 2015 8:43 PM (in response to William Choo)Hi Oleg,
Thanks for getting back to me on this! And I sure wished I had a copy of ur new book
Actually, I've managed to solve it using rangesum as an alternative to using full accumulation.
To elaborate on my earlier scenario which I didn't show in my example above, I had a CustomerID field as a unique key and I use "Customer" as a field name above to designate customer names
Graph 1:
YAxis:Rangesum(above(total ROUND(sum(Profit)),0,RowNo(TOTAL)))
XAxis: aggr(Rangesum(above(total ROUND(sum(Sales)),0,RowNo(TOTAL))),CustomerID)
Sort: Rangesum(above(total ROUND(sum(Sales)),0,RowNo(TOTAL)))
Graph 2:
YAxis Expression: Rangesum(above(total ROUND(sum(Profit)),0,RowNo(TOTAL)))
XAxis: Customer
Sort: aggr(num(rank(sum(Profit),4)),Entity_Cust), Ascending
Generally speaking, this met my requirement .
I would also play around with your instructions above to see any new graphs I can plot!
Thanks!