topic Re: Isolating High Sales by Month in QlikView

Isolating High Sales by Month

Wed, 18 Jan 2012 16:22:11 GMT

I would like to create a graph that shows what percent of each month's sales total was contributed by agents who had at least 50,000 in sales that month.

There is a table with a list of all the sales. It has the fields SalesAmt and MonthAgent (which is a key to connect to the Agent table). There is also a MonthAgent table, that lists the agents that are active each month.

As a simple example, let's say there are three agents and three months and a total of 20 different sales transactions. I've attached an example in Excel and the steps to arrive at the answer.

I'm pretty sure this can be done in QlikView, but am not sure where to start. I appreciate any help! Thanks

Re: Isolating High Sales by Month

swuehl — Wed, 18 Jan 2012 17:23:06 GMT

I think you can use something like

=sum(aggr(if(Sum(total<Month,Agent> [Sale Amount])>50000,[Sale Amount]),Month,Agent,[Sale #]))

as expression in a chart to get the Top Agents Sales. Then calculating the Percentage is just above divided by Total Sales per Month.

Re: Isolating High Sales by Month

Fri, 20 Jan 2012 19:20:29 GMT

Thanks Stefan,

I haven't had a ton of time to get back to this problem, but it seems like it is in the right direction. When I run it, though, given the millions of sales transactions, the server gets bogged down and qv doesn't respond.

Re: Isolating High Sales by Month

swuehl — Sat, 21 Jan 2012 14:49:25 GMT

Yes, on large data, the expression using aggr() will be probably not perfomant enough.

The problem is here, that we need to aggregate monthly sales per Agent to decide if we need to include or exclude the sales amount.

I noticed that my expression could actually be more simple:

=sum(aggr(if(Sum([Sale Amount])>50000,sum([Sale Amount])),Month,Agent))

Maybe this will improve a bit on performance.

If you could precalculate the monthly sales per agent in the script, you could then use a good performing set analysis to filter your records:

INPUT:

LOAD Agent,

Month,

AutoNumber(Agent&Month) as AgentMonthID,

[Sale #],

[Sale Amount]

FROM

comm45540_Agents.xls

(biff, embedded labels, table is Sheet1$);

LOAD autonumber(Agent&Month) as AgentMonthID, sum([Sale Amount]) as SumSales resident INPUT group by Month, Agent;

Then, an expression like

sum({<[SumSales] = {">50000"}>}[Sale Amount])

will return the top Sales only. The downside is, this will not reflect any selections in other fields you may have, like Product.