Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
matthewp
Contributor 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
MVP
MVP

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.



6 Replies
hirishv7
Honored Contributor

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

May be this:

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

matthewp
Contributor III

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

nope, this gives 3429

matthewp
Contributor III

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

nope, this gives 396

MVP
MVP

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.



matthewp
Contributor III

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

perfect

Community Browser