1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer

InMemory Measure to calculate daily sales in a period

Avatar
Sylvain Roy

Hello. Somebody must have come across this...

The InMemory database is based on Dynamics NAV data. We have a measure that computes the Sales Amount from within the Value Entry table. Making the sum of the Sales Amount for a given period of data, say a month is fairly straigthforward.

However, we would like to be able to compute the Average Daily Sales Amount for any period. To do so, we need to divide the Sales Amount by the number of calendar day in the period. We tried to create a measure that DISTINCT COUNT the number of posting date, but if there are no sales on a given date, it is excluded from the count. We also tried to use max(Posting Date)-min(Posting Date), but again, if there are sales only between two min and max dates in the period, we don't have a correct count.

Has someone found a way to accurately count the number of days within any given period as a measure in the data model?

Any suggestion would be much appreciated!

0

Comments

9 comments
Sort by Date Votes
  • Avatar
    Femke Kooij

    Why not create a measure that returns the datediff of 2 dates if you use calendar dates? This won't work if you're using working days, but with calendar days it should.

     

     

    0
  • Avatar
    Sylvain Roy

    Thanks for your input Femke. Could you elaborate on which two dates you would use? In the fact table, we only have one date which is the date at which the sales was posted.

    In the example below, you see that the day count [max(posting date)-min(posting date) +1] gives a day count of 1 and 26 for January. This can significantly distorts the data. Use the DISTINCT COUNT is even worst.

    0
  • Avatar
    Femke Kooij

    Hi how is your datamodel setup? Do you have a calendar tabel with all dates and then a fact with posting date and customer id and the sales value? Then you can create in your dwh a measure with count distinct calendar days within a month. Or another way: have in the calendar tabel an extra field  with the max posting days in that month (eg MaxPostingDaysInMonth). So every day record in January holds 20, every record in February holds 19. Create a measure in olap to get the max of that field , so  max(MaxPostingDaysInMonth) and name it #PostingDays, next you can create either in olap (Sum(Sales Value)/max(MaxPostingDaysInMonth) or in Targit  Sales Value / #PostingDays the metric you want.

    Just rember that you can only diplay this by Month, if you need Year, Quarter, Week  you need separate fields in calendar tabel for that.

    This presumably that you want the same number of posting dates for all clients.

    0
  • Avatar
    Sylvain Roy

    Thanks much for your input Femke. The datamodel looks like this:

    The calendar table is dPeriod and the fact table is fValueEntry. The other dimension tables contains information about the items sold and where they were sold from.

    I tried what I think you suggested by

    1. Creating a new cube based on the dPeriod table, which generally is a dimension table but in order to
    2. Create a measure which is a distinct count of TheDate, it also needs to be a cube.

    However, this new measure cannot be added to the fValueEntry cube (that contains Sales Amount) nor the Master Virtual Cube because it is linked from fValueEntry TO dPeriod. So I tried to create a copy of dPeriod, called fdPeriod to create the new measure, but then it only works on giving me day counts for the total:

    My guess is I would need to create a table that contains all the date-item-location combination in order to link fdPeriod to those other tables. In our environment, it is 6 000 000 lines PER DAY! To have 2 full years of data, it requires over 4 billions line!

    Other ideas?

    0
  • Avatar
    Sylvain Roy

    I actually tried creating all combinations for 2 years and the ETL throws :

    10:55:04.452 ERROR : Could not add SQL select TheDate, ItemNo, VariantCode, LocationCode
    10:55:04.453 from tItemLocation, tPeriodSubset from ME as table fPeriod Error executing SQL select TheDate, ItemNo, VariantCode, LocationCode
    10:55:04.454 from tItemLocation, tPeriodSubset Error Executing QuerySystem.AggregateException: One or more errors occurred. ---> System.IndexOutOfRangeException: Index was outside the bounds of the array.
    0
  • Avatar
    Boe Pedersen

    Hi Sylvain,

    I think you should be able to create the Daycount on the dPeriod table instead of on the fValueEntry.

    If you create a cube on the dPeriod and make row count on that table and use that for you calculation it should include all days regardless of it has sales on the day or not...

    0
  • Avatar
    Jay Scott

    This was an important hurdle we had to overcome in order to calculate the budgeted sales per day.  We needed working days.  So we created a measure the Data Model calculating working days such as: 

    case when Dayofweek(DIM_JournalPeriod.thedate) in (1,7) then 0 else case when DIM_JournalPeriod.thedate in ('05/31/2021','07/05/2021','09/6/2021','11/25/2021','12/23/2021','12/24/2021','12/31/2021','01/03/2022') then 0 else 1 end end  

    The individual dates are our holidays.

    0
  • Avatar
    Sylvain Roy

    Boe Pedersen, I created a cube on dPeriod, as well as a measure on TheDate (DISTINCT COUNT), but I was not able to add any shared dimension on that cube. The result is that the count is OK for the totals, but any other dimensions does not have a count... what am I doing wrong?

    Jay Scott, pretty much same question, how do you link the Budget Sales measure to the DIM_JournalPeriod? Also, do you have Budgeted Sales for every work date?

    Thanks all for your input!

    0
  • Avatar
    Jay Scott

    I created a cube for DIM_JournalPeriod to place the measure.  The only shared Dimensions in the cube is the date dimensions from DIM_JournalPeriod.  Dim_JournalPeriod is linked to Budget Sales by TheDate field.  We have Budgeted Sales by Month.  I use the No of Business Days Measure to calculate budgeted Sales per day.  See attached screenshot.

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post