7 Replies Latest reply on Mar 27, 2017 5:34 PM by Yuriy Fal

    LOD Calculations that compare a date inside the subquery to a date outside the subquery

    Graeme Frost

      Hi All

       

      I have set up an exact copy of my problem on superstore sales in the attached book.

      For each month I want to know how many customers ordered from us at any time in the previous three months.

       

      So this is the LOD query I have created

      {FIXED [Reporting Date]: COUNTD(if datediff('month',[Order Date],[Reporting Date]) < 3

           and datediff('month',[Order Date],[Reporting Date]) >= 0

        then [Customer ID] end)}

      Reporting Date is a calculated field on Order Date.  All the fields are as per superstore.

       

      I am getting the same result as if I just do a CountD of the customers for the month.  What I expected to happen (because I was specific about Reporting Date being Fixed) was that would refer to the Reporting Date in the outer query and Order Date would be the one in the sub query.  But that doesn't seem to work!

       

      The package workbook is 9.3 (which is what my customer is using) but I can open a response in any version.

       

      Any Help greatly appreciated

      Best Regards

      Graeme