Search code examples
qlikviewqliksenseset-analysisqlik-expression

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

Solution

  • Using the {1} prefix in the expression will ignore all selections from listboxes (or other) and the DISTINCT on the field that is being counted.

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