Working days and remaining days

This topic has 4 replies, 2 voices, and was last updated 3 years, 7 months ago by Frank.

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

      We’ve had some requests for average daily sales and run rates required for the remaining days in the month. In both cases you need to know the working days in the month in total, as well as the number of working days so far. There are some date calcs you can use in Stratum but they don’t take your specific work day calendar into account and they can be expensive from a performance point of view.

      If you would like to have the work day counts available in Stratum you should consider populating the System Control category values that are specific for total days and work days. Once you have these populated you can use them in calculations for things like avg daily sales this month, or year. Required run rate for remaining days to meet the plan etc.

    • #525
       Pat Franklin
      • Topics Started - 1
      • Replies Written - 2

      Frank, hoping you can provide some insight on how to accomplish this with the system control category. We have requirements to look at the number of working days and so far by customer, rep, region or a combination of them. I am not seeing how you can use the system control value category to correctly total the working days based on the flexibility needed to look at the data in different ways.

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

      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.

    • #527
       Pat Franklin
      • Topics Started - 1
      • Replies Written - 2

      Thanks Frank. I am able to get this to work as expected with your example above.

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

      Excellent! Apparently my response was slightly clearer than mud.

Viewing 4 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?