Banding Aggregations within several ranges

[#1]

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

[sunny_talwar]

May be try this:

Dimension:

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

Expression:

Sum(Sales)

[#3]

Hi Sunny,

Thank you for your reply.

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,

Thank you in advance.

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.

[sunny_talwar · Accepted solution]

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)

[#5]

This is exactly what I needed!

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

[sunny_talwar]

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