Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
May be try this:
Dimension:
Aggr(Class(Sum(Sales), 10), Invoice)
Expression:
Sum(Sales)
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.
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)
This is exactly what I needed!
Thank you very much - time to research "Dual".
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