Forum Replies Created
October 23, 2020 at 7:40 am #609
New way to easily exclude stuff in a view! Part of our upcoming 7.2 release will be a new exclude option for dimensions. You can quickly specify if a filter is to be an include (the default like it is now) or exclude (new option coming in 7.2) If you read the earlier posts in this thread you’ll find a couple of examples on excluding specific values in a filter. This new change will make it even easier. Here’s a screen shot of the updated Filter window.
August 11, 2020 at 3:11 pm #597
Supply Chain Metrics – The Perfect Order.
Following up to prior posts I remembered we did a presentation at a the JDE Quest user group conference (remember those – you traveled somewhere and met in person with lots of different people….) where we discussed some detail behind the Perfect Order metric. We used a pyramid diagram to depict different components of a KPI, in this case the perfect order. I first saw a similar depiction from Gartner at their supply chain conference (another one of those conference things….) and liked how it allows you to break a KPI down to it’s elements. For a dashboard you could start with a high level pyramid like the one on the left and then take a specific metric and break it down like the pyramid on the right. I know this can look daunting. But as you develop and collect the information for parts of the KPI begin to publish it and use it in dashboards. Dashboards are not a static collection of info, they should morph and change with your business and the availability and quality of information. For example in the Perfect Order KPI you may not have all the pieces – but just having the returns captured and seeing those as a % of sales can give you a ratio that can identify mfg or supply issues.
- This reply was modified 5 months, 1 week ago by Frank.
June 16, 2020 at 3:15 pm #589
What should I include on my dashboard – Supply Chain metrics
As I mentioned in my previous post here some ideas on supply chain metrics you may want to consider. This list is in no way exhaustive but it’s a good starting point as you think thru how to better digitally manage your supply chain.
These metrics and kpi’s are sometimes a bit tougher to compile, measuring your SC performance is critical.
As with any analytics project start small and work your way towards your goal. Remember that the kpi you want to measure today, may not be the one you want to measure tomorrow. Times they are a changin’! So gather information, leave room for flexibility and don’t worry if the measurement is not perfect. You can refine as you go.
So some supply chain metrics that we’ve seen both at customers and/or in sc dashboards:
Inventory turns and sales to inventory ratio
Both of these can help understand a number of different facets of your business – a key one being your planning and fulfillment processes and strategies. We see this one by year and by rolling x periods – both pretty easy in Stratum if you keep inventory snap shots. (if you don’t, but are seeing a single snapshot of inventory it’s pretty easy to start keeping historical snap shots).
DSO – days sales outstanding
Need to have your AR information – which is generally pretty easy to collect. Beyond the obvious how long to collect aspect of this metric you can use DSO as a part of an internal customer scorecard when evaluating customers. DSO also is part of the Cash to Cash Cycle Time KPI which is a more complex KPI but one that many SC dashboards contain – or at least they are working towards including it.
Sales to Forecast along with Outlook
This is an easy one that allows for basic comparison of sales (and orders) to the forecast and/or plan. From this simple metric you can determine where you are at vs. where you expected to be – you can look at it YTD, over time, current period etc. The Outlook lets you take expected sales (existing booked orders and the forecast, and anything else you might want to throw in – such as open CRM opportunities that are x% along…) to determine how the remainder of the year might play out. Be careful with the Outlook if you are off your forecast and have not made adjustments to your future forecasts.
Past Due Orders
Another simple one. All you need is open orders in Stratum and your definition of what is ‘past due’ With this you can begin to find problem products, vendors and processes.
Return amount and quantities – with reason codes if possible.
This allows you to see return rates – as a ratio of sales is one way to look at this – and identify why returns are happening. Some of this information will be used if you pursue the Perfect Order Rate which I’ll mention later. We’ve seen metrics around this take on more importance as major retailers are penalizing suppliers more and more for products that require returns.
Now on to two valuable but more complex KPI’s for the supply chain:
Cash to Cash Cycle Time
The shorter the better is the general look at this measure. You’ll need Inventory, Payables and Receivables to calculate this one. 3 ratios – Days of inventory, payables and receivables ae used to figure the CCCT. Working to decrease this metric can help improve all parts of the supply chain – procurement to sales.
Perfect Order Rate
This is one we’ve seen Gartner and others talk about quite a bit. In practice it can be a little elusive to quantify but it’s a great KPI to work towards as it requires an number of metrics to create and each of those if valuable in and of itself. I may post some more detail on this in a later post. The metric is the measurement of your supply chains ability to deliver the right stuff at the right time with no issues and the right documentation. Monitoring metric along with its components can be a way to reduce penalties from your customers (most often we see this in automotive from retailers).
So few metrics you can work on using information you may already have in your Stratum hub or maybe some of these will help you to determine what information is next for inclusion in Stratum.
Any questions or comments post them. Thanks for listening.
March 6, 2020 at 11:28 am #575
Cleaning up our views – any suggestions?
Of course! Got lots for suggestions…..
But the basics are:
1. Create a View Group (from the Admin group in Stratum) called Archives or something similar. Set the Active indicator to No (you have set this since the default is Yes).
2. Open View maintenance (from the Admin group). Go to the Last Used caption in the blue header of the window and click on it. This will order the views in in last used order. Any view that does not have a date in this column has not been used since…well for a long time. Most of the views will have a date and you can select all the views that have not been used since a particular date. Highlight the first one you would like to archive then hold the shift key down and select the last one you want to archive – this will select all in between the two, or hold the CTRL key down and click on each view you want to archive.
3. Click the folder icon at the top of the View maintenance list – this will bring up a list of view groups in alpha order. Select your Archive view group and this will move all the views you have selected from the view groups they are currently in to your Archive view group. This effectively removes them from the list of views that users are seeing, but allows you to still have a copy of them if they are ever needed or you want to keep them as possible examples.
February 21, 2020 at 8:36 am #574
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):
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
July 22, 2019 at 7:43 am #560
I think what you’re looking for can be found in the properties of the measure. If you look at the properties of the measure (right click on the measure header, last option is properties, or open the View Explorer and double click on the measure) the last property is ‘Total:’, change this from from Total (which preforms the calc for the measure) to Sum (this will sum the entries in the column).
Let me know if this works or is not what you are looking for.
- This reply was modified 1 year, 6 months ago by Frank.
June 27, 2019 at 4:17 pm #553
This is true – that Stratum will AND filters together by default when you specify them on the columns. But you can use filters on the Row or Columns – also called Axis Filters to OR multiple filters together. In addition with Row and Column axis filters you can use boolean logic, ‘and’ and ‘or’ multiple things together and filter on both dimensions and measures.
You can get to Row and Column axis filters a number of ways:
1. Right click on the word Row or Column in the Navigation Panel and you’ll have an option to Edit a filter
2. Click the big filter funnel icon in the menu at the top and select the Axis filter panel
3. In the View Explorer double click or right click on the Row or Column node and edit the axis filter in the properties window.
Let’s say that you are looking for dead stock – items that are not moving and you have two criteria:
1. the average weekly movement over the last 13 weeks is less than 100 units/week
2. There have no sales in the last 4 weeks
Step one is to have columns in your view that represent the rolling 13 week avg sales and the sales for the last 4 weeks. If you’ve got questions on how to add these I can add more detail, let me know.
Step two is to add the access filter for rows (assuming you have your products listed in the rows and measures in the columns – which is the std. what Stratum shows stuff.
The axis filter would look something like:
[Measures].[Data5 (Avg 13 Wk Sales)]<100 OR [Measures].[Data2 (Last 4 Wk Tot Wk 35 to Wk 38)]=0
You can easily select the measures in the filter window under the measures node and just place the 'OR' between them and you're good to go.
Hope this helps!
November 21, 2018 at 12:57 pm #532
An easy way to show only YTD months or weeks when displaying information with the months or weeks going across the top or down the side.
I wanted to add an update to this post. When I initially suggested that you can use an expression to filter the months to only see those months that have passed and the current month, so that the future months without sales don’t show up (which is nice when comparing TY to LY particularly in a chart) I was right (just to be clear) that you can do it this way – but it’s also not the easiest way to do this.
Instead of the expression filter you can use what is known as a “Named Set” filter. These named sets are available for the Time dimensions like Months, Weeks and Days (without any Year associated with them – as I used in the example). Named Sets are a list of time units (Months, Weeks, Days normally) that Stratum automatically updates to include months from the beginning of the year thru the current month. You can filter the Months dimension on a named set by selecting to filter Months, then change the “Filter Type” which you can do by selecting the More button in the filter window. Just select Named Set and then pick one of them. In most cases it won’t matter which named set you select, there is a named set created for each type of monthly data you import, but in most cases they are all the same.
Post if you have any issues or questions. Hope this helps.
October 15, 2018 at 9:22 am #528
Excellent! Apparently my response was slightly clearer than mud.
October 13, 2018 at 4:37 pm #526
I’m assuming you have populated the category and are able to see the working days by at least one level – probably by company. If this is not the case the rest of this post won’t help…. The problem comes when you want to look at the working days by another dim, as you mentioned – customer, rep or region, or some combo of those.
You’ll need to use the same technique that we use to see data like inventory that is that is not stored by a dim that you are drilled to – that example is here
I’ll paste the calc measure that I use to see the working days when I’m looking at a bunch of other dims below. This uses the idea of DefaultMember with a dimension in a tuple.
Here is the calc I’m using – you’ll need to substitute your dims and measure names. I have the working days stored only at the company level for company 100
/Make sure there is sales data for the row
Iif([Measures].[Data4 (Sales Amount Jan to Sep )]=0 AND [Measures].[Data3 (Plan Jan to Sep )] = 0 ,null,
//Work days are only available for company ? – so select those.
//work days are only at the Company level, so ignore other dims
[Customer SoldTo].[Customer SoldTo].DefaultMember,
[Distribution Channel].[Distribution Channel].DefaultMember,
[Product Category].[Product Category].DefaultMember,
[Product Type].[Product Type].DefaultMember,
[Product Family].[Product Family].DefaultMember,
[Product Brand].[Product Brand].DefaultMember,
[Measures].[Data10 (System Control Values Number of Days Sep 17 to Sep 17)]))
The Iif at the start is to make sure that there is data for a row, so the calc is not performed for null rows – this is needed to make sure you only see rows you want to see.
This calc allows me so see the working days for any of the dimensions listed that have the defualtmember in the member expression.
I’ve got a separate measure that is just the normal measure for the working days in Sept. – which is the Data10 measure used in the tuple.
It all this was meaningless or not helpful let me know and I’ll try again.
September 26, 2018 at 1:40 pm #522
Bryan – these types of questions are best handled via our Support Team. We’ll probably need more info than what you’ve posted and Support can help out with what you need.
September 25, 2018 at 8:03 am #517
Your test was successful! Welcome.
September 18, 2018 at 8:35 am #512
This question came up during a user review session recently. There are two solutions to this. The simplest is to include the Admin View Group in a users User Profile Group. Depending on the level of the user different options will appear in the Admin View Group. These are automatically set by Stratum and provide access to different functions for different levels of users. Here is a snippet from the Stratum Help that describes the access levels for different user levels:
Casual Users – have access to basic view features such as running views, editing existing sorts and filters, and saving views in that edited state as shared views or their own personal views. These users also have access to core view editing features via the Navigation Panel (change order of data, add levels, and more), the Filters window (add, delete, or change filters), and the Measure Items window (copy existing measure items to create new ones, show or hide measure items, and more).
Advanced Users – have access to all non-administrator functionality — they can build user lists, create user links, create actions and add actions to broadcast groups that their administrator has pre-defined for them, create conditional formats, make edits to existing views and save them as shared views or their own personal views, access view explorer, and insert new measure items and charts into views. Like casual users, advanced users can use the Navigation Panel, Filters window, and Measure Items window to control the data in their views.
View Administrators – have access to all non-administrator functionality (including what’s noted above for casual and advanced users) plus partial administrator access. That access includes setting up new views from scratch, setting up view groups, adding user profile groups using roles and user profiles already set up by a security administrator, setting up broadcast schedules and groups, and changing owners of various items in Stratum.Viewer (views, actions, broadcast schedules, broadcast groups, user lists, conditional formats).
Security Administrators – have access to all functionality including setting up roles and user profiles.
The second way to provide access to User Lists is to use a User Link and take users directly into the User List maintenance function from what ever View Group you add the User link to. The URL for use in the user link is the following:
http://yourserver:yourport#/UserListlistwindow.aspx – this link (after you substitute your server:port stuff) will take you to the User List list page where you can maintain and create new lists for any dimension.
The following link will allow you to create a user list for a specific dimension – it allows a user to go directly to the window for adding a new list. You’ll need to know the specific names of the dimensions you want to do this for, in this example it’s the dimension we call Product:
So a couple of ways to get access to the User Lists for advanced users.
Somewhere I also have a view that would allow you to build a list of, say, products based on filter criteria, then you could copy that list and the view has a link to go the create user list function, which in turn would allow you to paste your selection from the view into it. The view allows you to create lists for different dimensions and then links to the User List maintenance window for that dimension. If anyone is interested post something and I’ll see if I can dig it up.
September 18, 2018 at 8:02 am #510
This question came up in our open question Insight session a couple of weeks ago. The solution is to remove any conditional formatting you have in the view. When a measure has conditional formatting on it Stratum creates a set of merged excel columns so that the formatting can be included. If you remove the conditional format this will not happen, making the data in the column easier to use in excel.
Also – coming up in the next release – 6.9 – if you have a license to the Stratum Broadcast Server you will have multiple options for exporting to excel. One will be the same export available today – which is called an Excel Report. The new format is called Excel Data which will be exported with all the formatting removed, the values repeated on each row and no totals. By doing this Stratum is able to export large data sets much faster. The idea behind this new feature is to allow for exports that are made for information delivery, which are the Excel Reports that you can export today. The new Excel Data export allows for exporting data to be used as data in a pivot or other situation where a large amount of raw data is required. Other examples would be for exports to Power BI or to a table in excel.
May 1, 2018 at 1:56 pm #479