Creating new field data based on current selections

[rothk8675309]

Hi!

I am stuck trying to add a calculation that occurs whenever a user filters dimensions. My data consists of the following fields:

Region, Country, Salesperson, Number of Monthly Sales, Gross Monthly Sales

Whenever a new filter is applied, I would like the filtered data to be processed into a new table to be plotted on a chart. The process is as follows:

All rows are binned into groups by the number of sales. For example all the sales people who made 0-20 sales would be one bin, all salespeople who made 20-40 sales would be another bin and so on until the last bin includes the person who made the highest number of sales.

The new table would include each of the bins in the first field (0-20, 20-40, 40-60...)

The second field would be the average of "number of monthly sales" for that bin

The third field would be: Stdev of gross monthly sales for that bin + MAX of gross monthly sales for that bin

Proceeding fields would be other simple calculated values of each bin

I would then use the generated fields in a chart.

Any help is greatly appreciated!

[rwunderlich · Accepted solution]

Your starting point is to create a calculated dimension that buckets your salespeople.  Something like:

=aggr(class(sum([Number of Monthly Sales]), 20, 'Orders'), Salesperson)

The key here is the aggr(...,SalesPerson) function.  You are stating you want to do what's inside the aggr() function for each Salesperson.  So sum() the monthly sales and then use the class() function to assign that salesperson to a bin with width of 20.

After that, it's pretty easy. For example, expression avg sales for that bin is:

=Avg([Number of Monthly Sales])

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com