en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Justin Haber  
#1 Posted : Monday, April 27, 2015 2:26:02 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Forum
Joined: 2/6/2015(UTC)
Posts: 7

Hi,

I am wanting to review the results of Sales Invoices by Average Order Value, viewing the results by Average Order Value (AOV) groupings (by week). For example, I want to group all orders with AOV under £10, then £10 - £15, £15 - £25 etc.

AOV is simply calculated by dividing Sales Value by Number of Orders.

I can obtain information for each Sales Invoice by Day, however am struggling with writing an appropriate IF calculation to group individual orders to an AOV group.

This is what I can currently view in my report, showing Date, individual Sales Order, Amount, Number of Invoices and Average Order Value.

UserPostedImage

I want to add an extra column which shows the AOV group (e.g. Under £10, £10 - £15 etc.) and then to bring the AOV groupings onto the horizontal or vertical axis, showing by AOV Group and Week, the total Value of Orders and the Number of Orders, like this:

UserPostedImage

From the views you can see, I have got a Posting Date, each Posting Date belongs to a Reporting Week (and Month) within my Posting Period Hierarchy - I'd like the option on showing this by either Week or Month.

I’m struggling to write and appropriate formula for this, or maybe it can be done via a comparison, but again I can’t work out how/if you can write a comparison based on a calculation?

Any help appreciated.

Thanks.
Páll Bjarkason  
#2 Posted : Tuesday, April 28, 2015 2:52:18 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 154

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 setup
In 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].

UserPostedImage


Empty Comparison
To 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.

UserPostedImage


Custom calculations
All 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)

UserPostedImage


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 result
Screen 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

UserPostedImage

Visibility magic
This 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.

UserPostedImage


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

Best regards

Páll, CTS
Justin Haber  
#3 Posted : Wednesday, April 29, 2015 4:33:55 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Forum
Joined: 2/6/2015(UTC)
Posts: 7

I've customised this for my own report and has achieved exactly what I need to do - many thanks Pall

There are 2 extra things I want to do to this but need some more help!

1. I'm trying to put in a simple percentage split to show by Value Grouping comparison how much each comparison is worth by week, both for Revenue and Number of Orders - for example if on a specific week the £10 - £15 Value Group does £34,000, this would be 9.4% of the total of all the Value Groups in that specific week - same for number of orders.

I've tried to do this myself, but I think I need to build a calculation for each 'Posting Period Week' and not for all Dimension Values?

2. I have duplicated my report so that I can show this information as a Graph - but it just won't work for me. What I'd like to see on the horizontal axis is Week Number, on the vertical axis Visible Revenue (stacked), with the Value Groups being populated in the 'plotting area'. I only need to see the Visible Revenue column and not the Number of Orders in this graph - I have created this in excel..

UserPostedImage

Again, any help much appreciated!
Páll Bjarkason  
#4 Posted : Wednesday, May 13, 2015 11:54:47 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 154

1 Create a new custom calculation -> as a measure.
Submit the code below.
Format the new calculation numbers -> Number formatting -> Percent.
Repeat fore other measures.

UserPostedImage


2 To visualize a stacked bar chart, you can select the bar chart and then choose the multi series stacked type in chart properties.
On the down side the solution proposed is not well suited to be shown in a chart.
When using comparisons in charts you probably need some advanced visibility or extra columns to get a nice formatting. Some re-engineering is probably needed to get the decried result and it will be much easier for you to have a dimension specifying the value ranges and not use the comparison element.

Edited by user Wednesday, May 13, 2015 11:58:16 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Justin Haber  
#5 Posted : Tuesday, May 19, 2015 1:55:13 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Forum
Joined: 2/6/2015(UTC)
Posts: 7

Thanks for this Pall - I've managed to sort the % splits, but am still having problems with my Bar Chart - as you've highlighted, I think I need to convert these comparisons into actual dimensions.

Thanks again for your help on this subject.
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error