1 Reply Latest reply on Aug 8, 2016 10:38 AM by Stoyko Kostov

    How to Lookup a date from one column  in another date column

    lijin dev

      Guys,

       

      I'm stuck with a calculation which would help me pick dates from Calendar date column and check whether those are available in Logon date field (which is brought in by joining )

       

      Calendar date as all the dates along with the people employed  and  logon data wil have userid and logon dates if they login .

       

      My objective is to find how may people were absent on a particular day.  Some of the views helped we visualized the data . but I'm looking for some calculation ( like LOD)  which gets me an aggregated number and which can be used in other calculations

        • 1. Re: How to Lookup a date from one column  in another date column
          Stoyko Kostov

          Hi Lijin,

           

          The way you've organized your data, you can create a calculated field called "Absent" with definition COUNT([User ID])-Count([UserID (Secondary)]), and then drag Calendar Date to Columns and Absent to Rows.

           

          Null values in UserID (secondary) are automatically excluded, so you get what you want: people who have not logged on are counted as absent.

           

          I assume you joined your data on UserID = UserID (Secondary) AND Calendar Date = Logon Date.

           

          I'm attaching the workbook I produced. Hope this helps.