2 Replies Latest reply on Feb 18, 2016 9:06 PM by Jagjit Singh

    Count Missing Action Items

    Jagjit Singh

      Hi All,

       

      In the attached workbook I have 32 action items that are completed when a property is let. Users do not enter a date against all the action items, hence in the database table only those items which have an Actual Date appear.

      I want to create a view that will show (count) all the missing actions items per property (prop code) in the vacant details table. I setup a separate spreadsheet with all the action items and tried to blend the data but when I drag the prop code dimension I get *.

       

      For Example for Prop Code 1521, Action Items 15, 28, 29, 30, 31, 37,38,39,40,41,42,44,45 are not in the vacant details table and these show appear as a count against the Action Description.

       

      Any ideas how we can great a view to show all the missing items as this will help users to identified the missing actions.

       

       

      Thanks.

       

       

      Jag

        • 1. Re: Count Missing Action Items
          Shinichiro Murakami

          Jaglit,

           

          In this case as long as I know, you need to create Action Items x Prop Code combination as master data.

           

           

          Thanks,

          Shin

           

          9.0 attatched

          • 2. Re: Count Missing Action Items
            Jagjit Singh

            Hi Shin,

             

            That will not work as we have more than 1000 properties and will have to manually refresh the spreadsheet to the prop code. I thought blending would work as what is not matching would be returned as a NULL. The NULL values are what I'm looking for so I can highlight them in a view so teams can go back to the system and enter the missing dates.

             

            If I have to introduce the action items in sql, iam not sure how to create a sql view from a temp table as below:

             

            Declare @tempactionitems Table  (ActionNumber INT

             

            ,ActionDescription varchar(255))

             

            INSERT INTO @tempactionitems (ActionNumber,ActionDescription) VALUES

             

            (1,'NOT Termination Received')

             

            ,(2,'Tenancy Expected End Date')

             

            ,(3,'Pre-Vacate Inspection')

             

            ,(4,'Support Agency Notified')

             

            ,(5,'Keys Returned')

             

            ,(6,'Keys to Assets')

             

            ,(13,'Keys received by Assets')

             

            ,(14,'Void Scope Inspection')

             

            ,(15,'Raise Void Work Order')

             

            ,(16,'Keys to Contractor')

             

            ,(17,'Keys from Contractor')

             

            ,(18,'Post Void Inspection/PCR')

             

            ,(19,'ReWork to Contractor')

             

            ,(20,'ReWork from Contractor')

             

            ,(21,'Keys to Allocation')

             

            ,(25,'Keys received from Assets')

             

            ,(26,'Referred to Support Agency')

             

            ,(27,'Received from Support Agency')

             

            ,(28,'Commence Shortlisting')

             

            ,(29,'Offer Property')

             

            ,(30,'Show property')

             

            ,(31,'Reject')

             

            ,(37,'Offer')

             

            ,(38,'Show')

             

            ,(39,'Reject')

             

            ,(40,'Offer')

             

            ,(41,'Show')

             

            ,(42,'Reject')

             

            ,(43,'Offer')

             

            ,(44,'Show')

             

            ,(45,'Reject')

             

            ,(46,'Accept')

             

             

            Thanks

            Jag