Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

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.

=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.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.



View solution in original post

6 Replies
HirisH_V7
Master
Master

Hi,

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

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


HirisH
“Aspire to Inspire before we Expire!”
sunny_talwar

May be this:

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

matthewp
Creator III
Creator III
Author

nope, this gives 3429

matthewp
Creator III
Creator III
Author

nope, this gives 396

jagan
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III
Author

perfect