1 Reply Latest reply on Feb 5, 2019 7:56 AM by patrick.byrne.0

    How to ignore Date Filter on a LOD whilst using date as a dimension AND filter?

    Joseph Wibowo

      After reading this post: How do I get LOD to ignore date filter? , I am wondering if it is possible to do the following:

      1. Count the number of users who purchased a product in 2018 <- Let's call this "2018_Users"
      2. Create a field with the calculation: "# of users who purchased a product in 2019" / 2018_Users
      3. Show this on a weekly basis

      I use a LOD calculation to find the 2018 users using this:

      {EXCLUDE [Order Date]: COUNT(

          {FIXED [End User], [Account Name]: MAX(

              IF [Order Date] >= DATEADD('day', -365, DATETRUNC('quarter', TODAY())) AND

                 [Order Date] < DATETRUNC('year', TODAY()) AND [Is Sample?] = FALSE

              THEN 1

              ELSE NULL END

              )

          }

      )}

      This is fine and gives me what I want as shown below, but I want to show this on a weekly basis in 2019. So basically I want to keep the 2018_Users static regardless of the date filter I put on it.

      Measures
      20182019
      Count of 2019 Users0100
      Count of 2018 Users10001000

       

      If I add in a date filter for just 2019, it looks like this:

      Measures
      Week 1
      Week 2
      Count of 2019 Users3070
      Count of 2018 Users5050

       

      From what I understand, because EXCLUDE happens after dimension filters, it is performing the LOD calc on a filtered dataset of just 2019 users. Thus, the 50 shown is the count of 2018 users who reordered in 2019. How can I make sure that I get that 1000 shows up regardless of if I filter on date? I have to use EXCLUDE to ensure the LOD doesn't get influenced by Order Date. Is there a workaround for that?

      Please don't ask for sample data as this is a conceptual question, so there's no need to use my data to answer it.

       

      EDIT: I realized I could just hide the columns that aren't 2019, so this question is not needed.