6 Replies Latest reply on Nov 18, 2018 8:49 PM by john.f

    How to perform LOD with conditions?

    john.f

      Hi all,

       

      I have a fairly simple use case for Level of Detail calculation. I'm trying to find the max date within a dataset, by category, but I'd like to omit certain records (categories) from this operation.

       

      A quick dataset to illustrate below. I want the max date within Fruit and Meat. {FIXED[Shopper ID]:Max([Expiration date])} of course returns 11/15/2018 and 10/10/2018, but I am looking for 11/13/2018 and 10/4/2018 respectively.

       

      Shopper IDCategoryExpiration DateMax Date (based on Fruit and meat)
      1Fruit11/10/201811/13/2018
      1Fruit11/9/201811/13/2018
      1Meat11/13/201811/13/2018
      1Vegetable11/15/201811/13/2018
      2Fruit10/2/201810/4/2018
      2Fruit10/4/201810/4/2018
      2Vegetable10/10/201810/4/2018

       

      Eventually, for my visualization, I want to return only the records with this max date.

       

      Shopper IDExpiration Date
      111/13/2018
      210/4/2018

       

      Thank you in advance!

        • 1. Re: How to perform LOD with conditions?
          john.f

          Ok, so I've figured out the LOD -

           

          max_date = {FIXED [shopper ID] : MAX(IF [category] <> 'Vegetable' THEN [Expiration Date] END)}

           

          However, when I create a calculation (if [Expiration date] = [max_date] then 1 else 0 end) and use it as a filter, I do not see any values of '1', only 0 and null. This is despite the max_date LOD working correctly. Alternatively, it would be ok if I could perform a calculation on the resulting LOD, but it is not allowing me to mix aggregated and non-aggregated. Ultimately, I want to calculate the difference between Expiration Date and Max Expiration Date and run some simple descritive stats on this number.

          • 2. Re: How to perform LOD with conditions?
            Simon Runc

            hi John,

             

            So I think you just need to remove Category from the left side of the LoD. Something like

             

            {FIXED: MAX(IIF([Category]<>'Vegetable',[Expiration Date],NULL))}

             

            Your current formula is going to create the Max date per category, with the exception of 'Vegetable' as all dates there are set to null (due to the IF statement) and so the MAX of null is null.

             

            Hope that helps, but let me know if you can't get it working

            1 of 1 people found this helpful
            • 3. Re: How to perform LOD with conditions?
              john.f

              Thanks Simon, your LOD also works (updated below to reflect addition of shopper ID, please let me know if this incorrect) in calculating the max date correctly. However, when I add the calc to display only those records where expiration date = max date, there are no values '1'.

               

              {FIXED[shopper ID]: MAX(IIF([category]<>'Vegetable',[expiration date],NULL))}

               

              And apologies, Simon and others, I did need to update the original table to add the Shopper ID. Sorry about that.

              • 4. Re: How to perform LOD with conditions?
                Chris Chalmers

                Hey John,

                 

                It's a little odd that one of the results of your calc is "null". `if [Expiration date] = [max_date] then 1 else 0 end` only returns null if [Expiration date] or [max_date] are null. Can you send us a workbook demonstrating the problem?

                 

                I created this visualization without any null problems. Also find it attached.

                 

                ConditionalLOD.PNG

                 

                -Chris Chalmers

                1 of 1 people found this helpful
                • 5. Re: How to perform LOD with conditions?
                  john.f

                  Thanks Chris. Your solution certainly works for the mock data I'd created above, and makes perfect sense. Of course, this is a generalization of the actual data I'm trying to do this on. On the actual data, I still continue to get only 0's and NULLS when I try to show only max dates... baffling. Will now try to figure out how the mock data is any different from my (larger) actual dataset. Wish I could post it, but I'm concerned about confidentiality.

                  • 6. Re: How to perform LOD with conditions?
                    john.f

                    I got it! My "expiration day" was in a dateTIME format. Once I switched it to date format, my "only max dates" calc worked.

                     

                    Thanks again!