Disclaimer Grouping a Custom Calculation can be done, but the solution generally composes of a big crosstab, where most of it is hidden.
Please watch out for performance issues. This kind of problem is often best suited to being solved in the backend and then a dimension could be made available defining the £ range-intervals. The backend approach thus facilitates good performance and ease of use in the TARGIT BI Suite.
It will really aid your work to have the decried period bucket as dimensions. I.e. weeks or months period buckets respectively, as they are actively used in this demo.
The custom calculations grouping demo setupIn this demo which seems similar to your description there are 2 dimensions on the primary (y) axis and 2 measures providing No of Sales [measure 1] and Revenue [measure 2].
Empty ComparisonTo represent the intervals a comparison whit 3 empty bucket gives the intervals.
Under £50.000, £50.000-£500.000 and over £500.000. The method described supports further added horizontal buckets.
The empty comparison element multiplies the measures (columns) per comparison element.
Custom calculationsAll calculations used are custom calculation -> As a new measure.
Please note that the calculation “Allcount column number (Instructional)”, as the name implies, merely is instructional. The aim is to show the column count left to right. The left most column is given the value 1, the middle column value 2 and the most right column value 3. Note that this count actually is done on the three comparison elements.
Allcount column number (Instructional) [measure 3] allcount(d1:0, d1, m1)
The calculation “intermediate Value” uses the allcount to check for horizontal position and then evaluates the value into the tree value ranges.
intermediate Value [measure 4]if allcount(d1:0, d1, m1) = 1 and sum(0, 0, m2) < 50000 then sum(0, 0, m2) else if allcount(d1:0, d1, m1) = 2 and sum(0, 0, m2) >= 50000 and sum(0, 0, m2) < 500000 then sum(0, 0, m2) else if allcount(d1:0, d1, m1) = 3 and sum(0, 0, m2) >= 500000 then sum(0, 0, m2) else 0
The calculation “Visible Revenue” uses the sum all children to elevate the “intermediate Value” to the month total level.
Visible Revenue [measure 5]sum(0, all(c), m4)
The calculation “intermediate No of sales” check per row if the “intermediate Value” is greater than cero. If true the “No of Sales” is returned.
intermediate No of sales [measure 6]if sum(0, 0, m4) > 0 then sum(0, 0, m1) else 0
The calculation “Visible No of Sales” uses the sum all children to elevate the “intermediate No of sales” to the month total level.
Visible No of Sales [measure 7]sum(0, all(c), m6)
Intermediate resultScreen dump example of “Visible No of Sales”:
The month total of January is 34 consists of the row values of “intermediate No of sales“
(1+1+10+3+1+2+12+4)=34
Visibility magicThis intermediate result is a rather big, complex and unmanageable crosstab. Now it's time to see the built in visibility magic shine.
Via Properties -> Visibility the Format visibility options on the left hand side are toggled, resulting in and nicely formatted crosstab per month and £ range-intervals.
I hope that you get inspired to build your own solution.
Edited by user Tuesday, April 28, 2015 3:03:07 PM(UTC)
| Reason: Not specified