1 Reply Latest reply on Jun 30, 2016 4:18 PM by Yuriy Fal

    Exclude Partial month results from dataset using LOD

    JayC

      Hello All,

      Can someone please help me exclude partial month results from the final data-set using LOD.
      Last ()==0 will simply remove the unneeded data from the view every month but function depends on the layout of the table.

      i'd prefer to see a level of detail calc so logic is invariant regardless of the layout.

       

      Please help. I have not used LOD's before.

        • 1. Re: Exclude Partial month results from dataset using LOD
          Yuriy Fal

          Hi Jayachandra,

           

          First, let's define a "Partial Month".

          There could be more than one definition of it,

          each one would require a special logic.

           

          Here is one possible definition of Partial Month:

           

          1) It is the latest month in a datasource date field.

          Say [Ship Date] in a Sample Superstore has a latest month of Jan-2015.

           

          AND

           

          2) The Day Number of the latest day in a datasource date field

          is less than the Day Number of the latest day of a correspondent month.

          Say the latest date from [Ship Date] is 06-Jan-2015, which is less that 31-Jan-2015.

           

          So for the [Ship Date] field from a Sample Superstore datasource

          the month of Jan-2015 could be qualified as a Partial one.

           

          Second, let's write a filter to exclude all dates of Jan-2015 for [Ship Date].

          It could be simpler than that, but this one would be working, too:

           

          // Ship Date excl Partial Month

           

          IF

          DATEDIFF('day', {MAX([Ship Date])},

          DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month',{MAX([Ship Date])})))

          ) > 0

           

          THEN

          IIF(DATEDIFF('day',DATETRUNC('month',[Ship Date]),DATETRUNC('month',{MAX([Ship Date])})) = 0,NULL,[Ship Date])

           

          END

           

          The first part (IF ... ) compares the latest date of [Ship Date] in a datasource (06-Jan-2015)

          to the date of the latest day of the latest month of [Ship Date] in a datasource (31-Jan-2015).

          This is to apply both conditions 1) and 2) of the Partial Month definition above.

           

          The second part (THEN ... ) includes another (nested) IIF() statement.

          This is to convert every date of month Jan-2015 to NULL, other dates remain intact.

           

          If one place the above calculated field to Filters and choose Non-Null Values,

          all dates of January 2015 would have gone.

           

          Please find the attached wb.

          Hope it helps.

           

          Yours,

          Yuri