2 Replies Latest reply on Apr 13, 2016 9:00 AM by Shawn Wallwork

    Status Change Based on Dates

    Trys Harrison

      Hi everyone,

       

      I'm trying to create a report that will show the previous status of some data at a previous point of time. The lines of data are based on weekending dates and their status should not be counted until the date has passed. The report will include the last two quarters. For example, one line of data may have a weekending date of 7/2/2016. This means in the data for January is would not be counted as its not due yet. In February data it would be counted however, it was marked as no longer outstanding in March (when marked a date appears) and would not be counted in March total for outstanding.

       

      I'm sure there is a complicated formula that could be applied but I'm no step closer to fixing it!

       

      I've thought of trying to use a DATEDIFF of the weekending date and the date the status was changed along with a table calculation but it doesn't quite work.

       

      Any help much appreciated.

       

      Kind regards

       

      Trys

        • 1. Re: Status Change Based on Dates
          patrick.byrne.0

          Trys,

           

          I would like to clarify what is being asked. The report in question is going to essentially have the prior quarter or month or week summed and then the current quarters data will be shown at a more granular level?

           

          Also, if you could attach a packaged workbook that would greatly help with assisting with the case. Having a packaged workbook can really help guide through a solution and also increases the chances someone else in the community will be able to assist with the use case.

           

          Cheers,

          Patrick

          • 2. Re: Status Change Based on Dates
            Shawn Wallwork

            So Trys and I worked this out off-forum. It was an interesting issue.

             

            The Problem

            He had two different date fields in a single timesheet record. When the time sheet was created it generated the record with a Date field set to the current date-time (at that time), a Status field set to 'Pending', and Processed Date with a NULL value. Then when the timesheet was processed, the Status field was changed to 'Invoiced' and the Process Date field was set to the date-time the record was processed. (Note a new record was not generated, the original record was just updated.)

             

            The requirement was to determine how many timesheets were Pending 3 months ago. While some timesheets might still be Pending, most of the timesheet pending at that time have been changed to Invoiced, so the updated Status field couldn't help. Also there really wasn't a way to use any date math as Date would always be less than Process date for an individual record.

             

            The Solution

            We ended up using a self-union to consolidate the two date fields into a single field, and add a counter to both (one positive, one negative). Something like this:

             

            SELECT  *

                 , [View_Test].[Date] AS [Date]

                 , [View_Test].[Timesheet_ProcessedTimestamp] AS [Other Date]

                 , 1 AS [Count TS]

            FROM [dbo].[View_Test] [View_Test]

             

            UNION ALL

             

            SELECT  *

                 , [View_Test].[Timesheet_ProcessedTimestamp] AS [Date]

                 , [View_Test].[Date] AS [Other Date]

                 , -1 AS [Count TS]

            FROM [dbo].[View_Test] [View_Test]

             

            Then it was a matter of using a RUNNING_SUM across Date. When a new timesheet was entered in the system a 1 would be added to the [Count TS]. Once the timesheet was processed a -1 would be picked up, zeroing out the timesheet, meaning it was no longer Pending -- and this worked for any date in the past.

             

            The Wrinkle

            The solution worked fine except that we were using a date filter, so there were many timesheet that would start with a -1 and not have a matching 1 because the record was created earlier than the first date in the viz.

             

            Solving the Wrinkle

            I solved this using a LOD expression and a Context filter. Here's the calc:

             

                 { FIXED [Timesheet_ID] : SUM([Count TS]) } = -1

             

            I placed this on the filter shelf set to False. The problem with this calc on it's own is that as a FIXED LOD it by-passes the Date filter we have in place, and therefore sums across the entire dataset (5 years back), which produces only 0 or 1, but no -1. Remembering the filter order of execution, I added Context to the Date filter, which corrects the problem by moving the Date filter ahead of the FIXED calculation. So now the dataset is limited to the months in the filter, and summing along Timesheet_ID can now produce a -1.

             

            --Shawn

             

            [I'll probably post this as a blog soon, with a bit more detail.]

            2 of 2 people found this helpful