6 Replies Latest reply: Feb 1, 2016 3:40 AM by Matt Patterson RSS

    expression for last 12 months & DISTINCT & not changed by selections

    Matt Patterson

      I have a few expressions:

       

      =COUNT(SUPP)

       

      Gives me 9316 which is correct as it is the total number.

       

      =COUNT(DISTINCT SUPP)

       

      Gives me 3429 which is correct as it is the total distinct number.

       

      =ROUND(Sum( if((Date(MonthName(MMMYY),'MMM-YY')) >= (AddMonths(Today(),-13)),1,0)))

       

      Gives me 396 which is correct as it is the total number for the last 12 months JAN to JAN.


      =count({$<[Company] ={"$(=concat([Company],'","'))"}>}DISTINCT [SUPP])


      Gives me 332 which is correct as it is the total number for the last 12 months JAN to JAN but only when i have made the date selections.

       

      What I need:


      I need the following expression:


      =ROUND(Sum( if((Date(MonthName(MMMYY),'MMM-YY')) >= (AddMonths(Today(),-13)),1,0)))


      To be adapted into doing two things.

      1. Give the result for distinct selections which is (332)

      2. Not be affected by selections.

       

      Or using this expression :


      =count({$<[Company] ={"$(=concat([Company],'","'))"}>}DISTINCT [SUPP])


      To be adapted into doing two things.

      1. Give the result of 332 without needing to select JAN 15 to JAN 16.

      2. Not be affected by selections.