What are my sales – without that one category added in??

This topic has 2 replies, 1 voice, and was last updated 4 years, 9 months ago by Frank.

  • Author
    • #415
      • Topics Started - 80
      • Replies Written - 53

      We got a question about seeing the total sales minus a certain category of sales. It turns out this is a pretty easy calculation in Stratum if you have the category you want to exclude as a dimension in Stratum.

      For this example let’s say we’ve got 5 different Distribution Channels that we sell thru. Each sale we make and record in Stratum is associated with one of these channels in the dimension Distribution Channel. One of those channels is something we call Indirect Broker Sales. Now let’s say what we need to see is – Total Sales, Indirect Broker Sales, and Non Broker sales for each or our Product Categories and Markets.

      Because we want to see the Total Sales – we can’t filter on the Distribution Channel as this will cause us to see only those sales for the Distribution Channels in the filter. But we can use a calculation in Stratum called a “Tuple” to see only the Indirect Broker sales.

      The calculation is very easy to set up using the Stratum Expression window. In my example the calculation looks like this:

      ([Distribution Channel].[Distribution Channel].[Distribution Channel].[INB],[Measures].[Data1 (Actual Sales Sales Amount Jan 04 to Sep 04)])

      Yeah, yeah – I know – it looks a little daunting, but, really it was pretty simple.
      Once you have the expression window open here are the steps:
      1. Key in an open paren – “(”
      2. Open the Hierarchies and select (for my example) Distribution Channel – keep opening Distribution Channel until you see Members and click on members
      3. Select the member you are looking for, in my example Indirect Broker (INB)
      4. Key in a comma “,”
      5. Open the Measures group and select Actual Sales (or whatever your sales are called).
      6. Key in a closing paren “)”

      I further refined the calc with to the following – I did this so that the calc is only done when there is Sales data available – something you would most likely want to do as well for performance reasons. So the final calculation looks like (remember the lines that start with // are just comments to help remember what we are doing):

      //If there are sales – then calculate the Indirect sales by selecting
      //only those sales that are associated with our Indirect Sales
      // Channel = “INB”

      Iif([Measures].[Data1 (Actual Sales Sales Amount Jan 04 to Sep 04)]<>0,

      ([Distribution Channel].[Distribution Channel].[Distribution Channel].[INB],[Measures].[Data1 (Actual Sales Sales Amount Jan 04 to Sep 04)]),
      //If there are no Sales set the value to null

      The end result allows me to have a view something like the on below that provides information on Indirect Broker sales, Total Sales and Non Broker sales (this is the total sales minus our calculation for Indirect Broker sales).

      Have fun!

      Example Tuple View - Total, Broker and Non Broker sales with Product Category Charts

    • #418
      • Topics Started - 80
      • Replies Written - 53

      What if you want to add multiple members for a dimension together to get a total? For example what if you we wanted to add the INB (indirect broker sales as in the original post) and the Direct Sales (Distribution Channel = DIR) together. With a slight variation to the calc in the original post it’s easy to do this.
      If you know the members you want just go into the Expression window and in the hierarchy section work your way to members and select the members you want. Stratum will return a nicely formated string you can use in your calc. Or of course you can cut and paste get to the the same end result. What we want to do is use the Sum operator along with a member set. So instead of a single member in the tuple calc in the original post you would have:

      Sum({[Distribution Channel].[Distribution Channel].[Distribution Channel].[DIR], [Distribution Channel].[Distribution Channel].[Distribution Channel].[INB]},[Measures].[Data1 (Total Sales Jan CY to Sep CY)])

      We would still have the iif around this as in the original post but would use this instead of the tuple in that post. This calc will give use the sum of Distribution Channels DIR and INB.

      Hope this helps – leave a post if you need some help.

    • #419
      • Topics Started - 80
      • Replies Written - 53

      To follow up on the question above – the technique described above did not quite get what we were looking for. It provided the ability to use totals for the differing groups of Dist Channels but it did not provide all the functionality of creating a Dimension to roll the Dist Channels together differently. So we added an Attribute to the Dist Channel called Combined Channels that designated how the Dist Channels should roll up and then marked that attribute as a Virtual Dimension (attribute hierarchy) in the Connector. This allows users to see a new dimension called Combined Channels that works on all the information in Stratum where the Dist Channel is included. So all the current years and the historic without having to reload or update any transaction data. Soooo…..the Sum(tuple) idea – a good idea in some cases and another option is Virtual Dims.

Viewing 2 reply threads

You must be logged in to reply to this topic.


We're not around right now. But you can send us an email and we'll get back to you, asap.


©2022 KLEO Template a premium and multipurpose theme from Seventh Queen

Log in with your credentials


Forgot your details?