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

[matthewp]

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.

[HirisH_V7]

Hi,

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

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

[sunny_talwar]

May be this:

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

[matthewp]

nope, this gives 3429

[matthewp]

nope, this gives 396

[jagan · Accepted solution]

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.

[matthewp]

perfect