Can I add a simple calculation to a view

This topic contains 1 reply, has 1 voice, and was last updated by  Frank 3 months, 1 week ago.

  • Author
    Posts
  • #573
     Frank 
    Keymaster
    • Topics Started - 60
    • Replies Written - 49

    Just want to add a simple calc – the difference between a couple of columns.
    This is easy to do in Stratum via the Add Calculated Measure window.

    1st just a little terminology – in Stratum the numbers that you report – things like Sales Amount, Units, Inv. Qty, Budget Amount, PO Qty etc are called Measures. When you want to add a calculation we you’ll need to “Add a Calculated Measure item”.

    First you have need to get to the Add a Calc window
    Bunch of ways to get there:
    1. Most recent releases – hover or tap/click the 123 menu button at the top of the display – select Add Calculated Measure item.
    2. Right click on any currently displayed column – in the context menu go to Add then to Calculated Measure Item.
    3. If you’re a View Explorer user – first open the View Explorer – by clicking on the Folder with a Magnifying Glass icon at the top of the display. Once the View Explorer opens go down to the Measure node and right click – select Add a Calculated Measure item.

    There are probably other ways to get to the Add a Calc window but those are the 3 main ones off the top of my head – I tend to use number 1, because in the case of more complicated calcs I may be able to copy an existing measure and this menu allows me to do that as well. For the purpose of this post – we will not be copying – just adding a simple measure.

    Now that you’re in the Add window it’s simple to add the calc.
    1. Open the Entry in the left hand side of the window that says Measures. Click/tap on the arrow next to it.
    2. All the measures you currently have in the view are listed – both displayed (normal) and hidden (sometimes measures are hidden to they don’t show in the report but are used as part of a calculation). Find the one you want to subtract from, Let’s say Sales This Year, and click/tap on it. This causes it to show up in the right hand side of the window in the Expression box.
    3. Next add the minus sign (-) after the measure in the expression window.
    4. Find the other measure – lets say Sales Last Year – the one you want to subtract from the first one over in the left and click/tap on it, this will add it to the end of your expression.
    5. At the top of the window key in the column heading for the measure (in the Caption Expression box). If you want to automatically include dates in the measure click on the … next to the box. We’ll leave that for another post.
    6. Finally click on the drop down arrow in the Format window at the top of the window to select the formatting you want on your new calculated measure.
    7. Click Ok

    You’ll end up with an expression that looks something like:
    [Measures].[Data6 (Actual Sales Amount Jul TY to Sep TY)]-[Measures].[Data5 (Actual Sales Amount Jul LY to Sep LY)]

    The [Measures].[Data6 part is for Stratum to know what the measure is. The (Actual Sales Amount Jul TY to Sep TY) part is for you – so you can understand what measure you are using.

    Now that measure is in your view – Click Save!!!! This is the first icon at in the menu at the top.

    Now that that’s done you can move the measure around by dragging it where you want it either in the displayed grid or in the Measure Item window (when you click on the 123 icon) or in the View Explorer Window.

    Post any questions you have in response.

  • #574
     Frank 
    Keymaster
    • Topics Started - 60
    • Replies Written - 49

    Percentage calculations? Those are easy too!
    Following the steps in the previous post – get to the Add Calculated Measure window.

    Once there in list on the left side of the window open the Stratum Viewer Functions and then select one of the percentage calcs (I copied some info from the Stratum Help):
    Achievement Percent:
    Returns the achievement percentage between two measure items — for example, the percent of sales achieved in comparison to the budgeted sales.

    The expression for this function is Measure Item 1 / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Measure item 2, the divisor, is zero or null.

    The expression syntax includes the names (Data1 and Data2) and captions of the specified measure items.

    Recommendations: select a percentage Format String and set Total property to None.

    Divide with Zero Check:
    #DivideWithZeroCheck([Measures].[Data1 (Daily Sales Amount Wk 38 2014 to Wk 38 2014)], [Measures].[Data2 (Daily Sales Units Wk 38 2014 to Wk 38 2014)])

    Divides two numbers with a divide by zero check.
    The expression for this function is Measure Item 1 / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Numeric Expression 2, the divisor, is zero or null.

    Percent of Change:
    #PercentOfChange([Measures].[Data1 (Actual Sales Amount Q1 2013 to Q3 2013)], [Measures].[Data2 (Actual Sales Sales Amount Q1 2012 to Q3 2012)])

    Returns the percent of change, also known as the variance percentage, between two measure items or expressions — in this case, the change between YTD sales for two different years.

    The expression for this function is (Measure Item 1 – Measure Item 2) / Measure Item 2 with a divide by zero check. The divide by zero check will return null if Measure item 2, the divisor, is zero or null.

    Recommendations: select a percentage Format String and set Total property to None.

    The one I use most often is Achievement Percentage – how did we do vs. a plan or last year. These measures are one of the most common forms of a KPI or target metric and are good canidates for a Conditional Format (traffic light) and/or an exception view (one that only shows exceptions to some threshold – e.g. all customers for this rep that are more than 15% off of last year, or anywhere the product mix for this customer has changed by more than 10%.)

    When using these Stratum makes it easy to include the measures into the calc.

    Once you click on the % calc you want in the left side menu it will put that base definition in the right hand part of the calc window.
    Next open the Measure group in the left side of the window – this will list the measures you have in your view. Click on the one you want to divide – it will automatically be included into the expression in the right side of the window.
    Next click on the measure you want to divide by – it will it will automatically be included into the expression in the right side of the window.

    click ok – click Save if your good with results

    Done!

You must be logged in to reply to this topic.

CONTACT US

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

Sending

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

Log in with your credentials

or    

Forgot your details?