1 Reply Latest reply on Oct 14, 2016 8:35 PM by Sherzodbek Ibragimov

    Look up another table and count

    Archie R

      Is it possible to do something like a vlookup on another source and count rows based on conditions. Example below :

       

      Source 1

      Month-Year
      Month Start Date
      Month End Date
      Jan 20161/1/20161/31/2016
      Feb 20162/1/20162/28/2016
      Mar 20163/1/20163/31/2016

       

      Source 2

      Id
      Open
      Close
      11/1/20162/14/2016
      22/1/20162/28/2016
      32/15/20163/16/2016
      43/14/2016

       

       

      Report I am lookling for is 

      Month-Year
      Month Start Calculation
      Jan 2016

      For Jan 2016, calculate

      Count of Ids from Source 2

      where source 2.open date <  source 1.month start date  AND

      source2.close date > source 1.month start date

      Feb 2016For Feb 2016 , calculate etc
      Mar 2016
        • 1. Re: Look up another table and count
          Sherzodbek Ibragimov

          Archana,

          Usually, date is not a good option when you blend two sources. Usually, unique dimensions, like IDs would be an ideal option to blend two data sources. I think i know what you are trying to achieve. if it is not a big deal, would be able to write something like this to get your result for January and for the rest of months:

           

          COUNTD(IF [Open]<DATE('1/1/2016') AND [Close]>DATE('1/1/2016') THEN ([ID]) END)

           

          PS: I am not sure what kind of report is that, but it didn't make sense why you want to calculate IDs by this formula:

           

          open date <  month start date  AND close date > month start date