1 Reply Latest reply on Jul 21, 2017 10:58 AM by Tom W

    How to exclude dimensions in time series if current month is 0

    Daniela Shasha

      I have a table from which I want to exclude jobs that have no hours spent in the most recent month. There must be a simple solution that I'm not thinking of. I looked into creating sets. Do I have to create a parameter to get this to work? 

        • 1. Re: How to exclude dimensions in time series if current month is 0
          Tom W

          ZN(

              SUM(

                  IF

                      {FIXED [Job] : MAX(DATETRUNC('month',Date))} =

                      {FIXED : MAX(DATETRUNC('month',Date))}

                  THEN

                      Spent

                  end

                  )

          )

           

           

          >0

           

           

          Break it down;

          • ZN() will return 0 if the calculation inside ends up being null
          • I'm taking a SUM of a calculation
          • The IF statement is checking two level of detail calculations against each other (the level of detail calculation being the pieces inside the { }
            IF the highest month (MAX) for the Job equals the highest month in the entire dataset, then return the Spent field.

          • Because we wrap that IF in the SUM, we're effectively doing a SUM of [Spent] WHEN the maximum month for the job matches the maximum month for the entire dataset
          • At the end I'm checking if the SUM(....) is greater than zero. Thus this formula will return TRUE or FALSE
          • Why am I using datetrunc? I'm using datetrunc to standardize your date field at the month level as daily sales would throw this off.

           

          Put this new calculation on the filters shelf and set it to true.