7 Replies Latest reply on Feb 13, 2018 10:03 PM by Renata Jurica

    LOD INCLUDE and filters

    Renata Jurica

      I am fairly new to Tableau, so this may be a simple problem, but I and am getting very stuck so hope that someone here may have a good idea for me. I am trying to create a dashboard where I display distinct count of users at the last day of the year, or last day of the month, or last day of the week .

      This flexibility is important as some of my users are only interested in monthly data, while others need to get week's worth of data.

      I found this tutorial Top 15 Tableau LOD Expressions (Practical Examples) , and number 9 seemed to be exactly what I needed which I implemented.

       

      However, for my dashboard, I also need to provide filtering on other dimensions (4 of them), and this is where I started seeing problems with the above solution. I quickly learned that this is because of the filtering order in Tableau, i.e. dimensions filters are applied before the LOD calculation. Suddenly I was no longer getting last day of the week/month, but just the last day for the data set after the dimension filter is applied.

       

      For example here is what I want to be able to do with my dashboard:

      1. total number of distinct users on the last  day of the week/month

      2. total number of distinct users on the last day of the week/month that are still 'active'. If there are no active users on the last day of the week/month, I want to see 0. What I get is the last date in that week/month when one of the users was active.

      etc...

       

      Hope this is not too confusing... I attached a small dashboard that demonstrates the problem when filtering is applied.

       

       

      Is there any way to implement something like number 9 in the Top 15 Tableau LOD Expressions (Practical Examples)

      but also allow for filters to be applied to the set after LOD calculation.

       

      Are there any other options?  Thanks in advance...

       

      I am using tableau desktop version 10.3.5.

        • 1. Re: LOD INCLUDE and filters
          Yuriy Fal

          Hi Renata,

           

          Could you please post a packaged workbook --

          the one with the  *.twbx file extension?

           

          The MaxDatePerMark.twb file you've posted

          contains no data.

           

          Yours,

          Yuri

          • 2. Re: LOD INCLUDE and filters
            Renata Jurica

            here is the new attachment.

            • 3. Re: LOD INCLUDE and filters
              Yuriy Fal

              Hi Renata,

               

              Thank you for the workbook.

               

              There are two main 'properties' of your data

              which requires a slightly different treatment

              than that of the Tableau Top 15 LOD # 9.

               

              1) The data granularity.

               

              # 9 has it defined as a row "per Ticker per Day".

               

              Your data have a row "per ID per Status per Day" --

              when (if) you're using the [Status] as a Dimension

              (for example, filtering just 'active' users for a period

              by applying [Status] filter as a regular Dimension one).

               

              2) The data sparseness.

               

              The data in # 9 are dense.

              Every possible combination of the unique values

              of all Dimensions are present in the data (have rows).

               

              Your data are sparse -- again if the [Status] column is

              (it's unique values are) taken into account.

              To make it dense (at least for the particular analyses)

              one should have a row in the data for every combination

              of unique values of all Dimensions involved.

               

              In your case -- since we're not filtering / grouping by IDs --

              there should be at least one row for every Status on every Day.

               

              This kind of densification could be done in the datasource --

              by adding (via UNION) the rows with the same 'immortal' ID --

              call it Duncan MacLeod :-) -- which exists (simultaneously)

              in every possible Status on every Date.

               

              Then the COUNTD( [ID] ) - 1 would give the correct result

              everywhere (including those rainy days with no 'active' IDs,

              the result on those days would be 0).

               

              With that in mind the original calcs from the # 9

              could be applied to your data -- but with minor modifications.

               

              Please find the attached.

               

              I skipped the densification issue & workaround --

              in a hope that in the real-world data you'd have

              at least one 'active' ID for each (work) Date,

              otherwise what would you do with your business :-)

               

              Hope it could help a bit (rants aside).

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: LOD INCLUDE and filters
                Renata Jurica

                Thanks so much, I will have a look and let you know! I really appreciate you taking time to look into this.

                 

                Also, data is sparse in my example workbook, as I made it just for the purpose of showing what the issue was.

                In my real life example, the one I am trying to solve, granularity and density won't be an issue.

                • 5. Re: LOD INCLUDE and filters
                  Renata Jurica

                  Thanks so much, your response was very helpful indeed. I also learned a little bit more about how to use LOD EXCLUDE.

                  • 6. Re: LOD INCLUDE and filters
                    Yuriy Fal

                    Renata, you're welcome.