4 Replies Latest reply on Dec 15, 2016 5:59 AM by Jelle Eitjes

    Average calculation on weekday view using LOD expression doesn't make sense

    Jelle Eitjes

      Hello,

       

      I'm trying to display the average amount of visitors to an Amsterdam library per weekday (Monday-Sunday). My line data of visitors coming in are detailed to intervals of 10 minutes. I used a LOD expression to sum the visitors by day and now want to average this data in a weekday view.

       

      If I create a DAY (1st screen shot) I get the expected average of a Monday when filtered for only mondays (I ran these numbers in excel to check) = 2272 per day

      If I use a WEEKDAY view (2nd screen shot) I get this odd number that I can't figure out.

       

      I need to deliver a bar chart as in the 2nd view, so really hoping someone could be of help to this

       

      I attached the workbook as well for complete reference.

       

       

       

       

        • 1. Re: Average calculation on weekday view using LOD expression doesn't make sense
          Simon Runc

          hi Jelle,

           

          So I've amended your LoD formula to use DATETRUNC on 'day' rather than DATEPART...and now the 2 views both show the same.

           

          I've only got a bit of time today, so wanted to get you an answer posted, but will have a bit more time tomorrow to go into the why's

          1 of 1 people found this helpful
          • 2. Re: Average calculation on weekday view using LOD expression doesn't make sense
            Jelle Eitjes

            Thanks this is great already!

             

            If you could enlighten me with the why, that'd be even better

            • 3. Re: Average calculation on weekday view using LOD expression doesn't make sense
              Simon Runc

              hi Jelle,

               

              So got a bit more time today...

               

              So there are a couple of things here...Firstly the 'day' key word in DATETRUNC and DATEPART do different things.

               

              In DATETRUNC the datetime is truncated to only be at day level (so it returns a date), whereas 'day' in DATEPART returns the day of the month (so 1,2,3...31), so returns an integer...'weekday' is the keyword for DATEPART which returns the Day of Week (Monday, Tuesday, Wednesday...), this one returns a string

               

              So in your formula, it was adding up all the 1st of the months, all the 2nd of the month...etc. and then displaying these by day of week (so a Monday may consist of month day 4, 9, 24...etc.). If we change the formula to be 'weekday' (keeping the DATEPART) that will create a SUM of all the Monday's, all the Tuesday's...etc. which means that when we look at this by DayOfWeek, it's just the same as the standard SUM...so here the LoD has summed up all the Monday visitors (129,500) and then taken an average of this (129,500)

               

               

              When we use DATETRUNC all this does is roll the data up...if we have our data by hour, say, it aggregates it all to day, like the below (and would also add up each ID within that...so we get a single returned value for each day, even if that day is made up of multiple rows)

               

               

              This means when we get the SUM per day...once we have this, taking the average gives us the Average Daily sales, and then including the DayOfWeek we get this split by Monday, Tuesday....

               

              Hope that helps, but let me know if anything doesn't make sense.

              1 of 1 people found this helpful
              • 4. Re: Average calculation on weekday view using LOD expression doesn't make sense
                Jelle Eitjes

                Hi Simon,

                 

                Great man! You've made it crystal clear to me

                 

                You're a legend, thanks

                 

                All the best,

                 

                Jelle