3 Replies Latest reply on Jun 29, 2016 4:24 PM by Christina Gremore

    Date Parameter minus 1: viewing previous day's data

    Owen Davenport

      Just for a bit of background,  I have a table that has a From Date and a To Date, and I wanted to be able to view only what was true as of a given date BETWEEN those dates.  It was simple enough, I created a date parameter, and then created the following calculated field:

       

             [Date] >= DATE([From Date])

      and [Date] <= DATE([To Date])

       

      I then made that a filter, and I have a nice clean view of my data for any date I select.

       

      The problem I'm running into is that I want to also see what was true as of the day prior ("yesterday"), IN ADDITION to my current day, so I can calculate the difference between the two (e.g. count how many IDs were in the table yesterday -/+ how many IDs are in the table today).

       

      I've tried several things, but ideally I'd want it to be something like this:

       

      COUNT(IF [Date] -1 >= DATE([From Date])

      AND [Date] -1 <= DATE([To Date]) THEN [User ID] END)

       

      This runs fine, but the IF statement doesn't actually limit the count for the previous day.  I tried doing a WINDOW_COUNT, but I can't seem to get it right.

       

      My background is in SQL, which has a very simple solution, but I don't think the logic works the same way.  In SQL it would look like:

       

      select count(User ID)

      from [table]

      where [variable_date] - 1 between from_date and to_date

       

       

      Is there a solution to this?  Is a WINDOW_COUNT the right choice, and if so, how would I do it?