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

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

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.

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:

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.

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

Hi,

If your report don't want to change per selections means,

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

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

nope, this gives 3429

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

May be this:

=Round(Sum({1<Company = \$::Company>} If((Date(MonthName(MMMYY),'MMM-YY')) >= (AddMonths(Today(), -13)), 1, 0)))

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

nope, this gives 396

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

Hi,

Try like this

=Count({1<MMMYY ={'>=\$(=Date(AddMonths(Today(), -13),'MMM-YY'))<=\$(=Date(Today(), 'MMM-YY'))'}>} DISTINCT [SUPP])

Hope this helps you.

Regards,

Jagan.

• ###### Re: expression for last 12 months & DISTINCT & not changed by selections

perfect