5 Replies Latest reply: Aug 31, 2016 1:06 PM by Sunny Talwar

# Banding Aggregations within several ranges

Hi All,

I may be thinking about this entirely the wrong way, but I am trying to report total sales based on basket value bands. For example, for all orders below £10, we generated a total of £100, orders from £10-£20 we generated £500 etc.

Currently, I have the following as a dimension:

aggr(sum([Sales]), [Invoice]) //**To total line by line sales values against each invoice (ie total order value).

Initially I was then attempting to band them as follows:

sum({<[Sales]={">=0 <10"}>} [Sales])

This however left me with 0. So I decided the best bet would be to try to create the bands within the dimension and simply use the following measure:

sum([Sales]).

However I cannot think how to create the bands. Any assistance will be greatly appreciated.

Kind regards,

Michael

• ###### Re: Banding Aggregations within several ranges

May be try this:

Dimension:

Aggr(Class(Sum(Sales), 10), Invoice)

Expression:

Sum(Sales)

• ###### Re: Banding Aggregations within several ranges

Hi Sunny,

Unfortunately this seems to just display the sum total for each order rather than banding them.

Basically, I am looking for 10+ bands (£0 - £10, £10 - £20, £20 - £30 etc.) which refer to the basket value of each order.

Within those bands, I am trying to report the total sum of sales for all orders with a basket value within those bands.

Hope you can help,

EDIT

Nevermind, this does actually band them, however I have no control over the bandings. Do you know if I can specify the individual brackets? For example, after 40 - 50, I might want 50-75. Otherwise, do you know if I can force the bandings to appear in incremental numeric order to a limitation of, say, 10?

Thank you again for your assistance.

• ###### Re: Banding Aggregations within several ranges

Use if statement in that case

Aggr(

If(Sum(Sales) > 0 and Sum(Sales) < 10, Dual('0-10', 1),

If(Sum(Sales) >= 10 and Sum(Sales) < 20, Dual('10-20', 2),

If(Sum(Sales) >= 20 and Sum(Sales) < 30, Dual('20-30', 3),

If(Sum(Sales) >= 30 and Sum(Sales) < 40, Dual('30-40', 4),

If(Sum(Sales) >= 40 and Sum(Sales) < 50, Dual('40-50', 5),

If(Sum(Sales) >= 50 and Sum(Sales) < 75, Dual('50-75', 6))))))), Invoice)

• ###### Re: Banding Aggregations within several ranges

This is exactly what I needed!

Thank you very much - time to research "Dual".

• ###### Re: Banding Aggregations within several ranges

Dual is to maintain the sort order, this would have worked without the dual, but then you would have worried about getting the order of ranges right