2 Replies Latest reply on Jan 6, 2014 6:33 AM by Jessica Lee

    Getting number of records of today, yesterday, and same day a week ago.

    Jessica Lee

      Hi all,

       

      I am trying to get a number of records for today, yesterday, and same day a week ago.

      Instead of adding filters, I want this to be updated automatically.

      So, I wrote a function to identify days.

       

      IF day([load_date])=day(DATEADD('day', -1, today())) THEN 'today'

      ELSEIF day([load_date])=day(DATEADD('day', -2, TODAY() )) THEN 'yesterday'

      ELSEIF day([load_date])=day(DATEADD('day', -8, TODAY() )) THEN 'a week ago'

      END

      **I'd like to mark a day before as a today.**

      So, like let's say today is January 3rd. Then I want to mark January 2nd as a today, January 1st as yesterday, and then December 26th as as same day a week ago.

       

      So the function above worked fine during the month of December since it is withing the same month.

      But as the date moved to new month, I ran into the problem.

      The problem is that not only January 1st&2nd, but also December 1st&2nd are marked as today & yesterday.

      I think this is happening because I am only using day to identify today, yesterday, and a week ago day.

       

      What I am trying to do is:

      1. Identify today, yesterday, and same day a week ago.

      2. Get number of records for each day.

      3. Calculate the difference between today's record and yesterday & difference between today and same day a week ago.

       

      This needs to be automated since I am planning to send out complete dashboard to other users.

       

      I used the similar method to do weekly comparison, but it worked fine.

      But I ran into this problem as I drill down into days.

       

      Can anyone suggest me a way to solve this problem?

       

      Thank you.