5 Replies Latest reply on Jan 15, 2019 11:40 AM by Shaquelle Jacobs

    Filters

    Shaquelle Jacobs

      I created a 12Months this year calculation.  I Replace Today() with a parameter because i want the user to be able to select the date. By doing so i have two date filters. How can i get one date filter only ?

      Mileage

       

      if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then [Daily Miles]

       

      END

       

      Road Call

       

      if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then [Daily Rc Cnt]

      END

       

      MDBF

       

      sum(if ([Date Reported]) >= DATEADD('month',-12, [Selected Date ])+1 then [12 Months Mileage This Year ] END)

      /

      sum( [12 Months RC This Year ])

        • 1. Re: Filters
          Joe Oppelt

          Make one calc:

           

           

          if [Date Reported] >= DATEADD('month',-12,[Selected Date ])+1 then 1 else 0

          END

           

           

          Put that on your filters shelf and select for value = 1.

           

          All the measures will be filtered to those dates.

          1 of 1 people found this helpful
          • 2. Re: Filters
            Shaquelle Jacobs

            Thank you very much Joe

            • 3. Re: Filters
              Shaquelle Jacobs

              what about previous 12 Months 

              Mileage

              if

              [Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

              and

              [Date Reported] <= DATEADD('month',-12,[Selected Date ])

              and

              NOT ISNULL([Fixed Level Of Detail Expression for Mileage  ])

              then [Daily Miles] END

               

              Road calls

               

              if

              [Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

              and

              [Date Reported] <= DATEADD('month',-12,[Selected Date ])

              and

              NOT ISNULL([Fixed Level Of Detail Expression for RC ])

              then [Daily Rc Cnt] END

               

              mdbf

               

              IFNULL(INT(sum(if [Date Reported] >= DATEADD('month',-24,[Selected Date ])+1 and [Date Reported] <= DATEADD('month',-12,[Selected Date ]) then [ 12 Months Previous Mileage ]

              end)

              /

              sum( [ 12 Months Previous RC])),0)

              • 4. Re: Filters
                Joe Oppelt

                I think I need to see a sample workbook to understand how 12 vs 24 come into play.


                The point is that if you filter to the dates you want, all the measures get filtered along with that.  You don't need to mess with calcs to limit the data in the measures.

                 

                 

                 

                So maybe:

                 

                if

                [Date Reported] >= DATEADD('month',-24, [Selected Date ])+1

                and

                [Date Reported] <= DATEADD('month',-12,[Selected Date ])

                then 1 else 0 END

                1 of 1 people found this helpful
                • 5. Re: Filters
                  Shaquelle Jacobs

                  i will put together a workbook and upload it to you in the morning.

                   

                  Thanks