Distinct count over last 12 months, with Months as column dimension, only show selected month(s)

[leenlart]

Hello!

I am trying to create a table showing the count of target clients and the count of clients with an order above a certain level.  And I've run into a problem that is driving me crazy!  

The lines of my pivot chart are the Salespeople, the columns will be the selected Month (or months 2 or 3 depending on target scenarios).  

The two expressions are distinct, and will have to be shown for each month selected.  (The optimal thing would be to only show Expression 1 in the total, but that's a whole different can of worms...).  

Expression 1 : distinct count of target clients (target clients = clients active over the last 12 months, and that have the target value as OUI.  

Count ( {<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'},[REG CODE NIV1 CIBPRIO1]={'OUI'}, DATE_JOUR={">=$(v12MoisDebut)<$(vAujourdhui)"} >} distinct %CLIENT_ID )

My problem here is that we only actually want to show the total count of target clients from 12 months before to the end of the selected month(s).  As I have to show the months in the table, its been agreed that its ok to have the total target the same if there are multiple months selected (and therefore shown in the table).  However, I can't get the total the same over two or more months!  In my example below, the total expected is shown in November.  But if the client hasn't purchased in December, they are not counted in the December count.  I tried to add a < today's date variable which is set based on the latest month selected, but that doesn't cut it.  Any ideas ?  

Expression 2 : A separate count by month.    

So in the example here, I'd like to have the DN Clients Cible be the same in both November and December.  Or magically change qlikview so that I can only show this expression in the Total.  😉

Thanks for any ideas!!

[Anil_Babu_Samineni]

Regards 2nd one, What is the expression you used? can you demonstrate in sample application to resolve quicker (If you can)?

[leenlart]

Here is the second expression :

count({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>}
distinct if(
aggr(sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]), %CLIENT_ID)>=$(vINC2_VOL_MIN),%CLIENT_ID))

I'll see how easily I can squish down my data to create a sample...

[Anil_Babu_Samineni]

May be change from Count to Sum and check and removed the DISTINCT as well? 

Sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>}
 if(
aggr(sum({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'}>} [TONNAGE VENDU]), %CLIENT_ID)>=$(vINC2_VOL_MIN),%CLIENT_ID))

[leenlart]

I don't think I explained this very well.  

Expression 2 works correctly and returns the expected data.  

My problem is with expression 1 , I'd like it to give the same data for both months November and December.

Count ( {<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'},[REG CODE NIV1 CIBPRIO1]={'OUI'}, DATE_JOUR={">=$(v12MoisDebut)<$(vAujourdhui)"} >} distinct %CLIENT_ID )

Here is a sample of my data and table.  I tried to make it as small as possible while still being able to show something meaningful.  

Thanks for your ideas.  

[Anil_Babu_Samineni · Accepted solution]

What about this?

If(MOIS=Max(TOTAL Num(Month(Date#(MOIS,'MMMM')))) or MOIS=Max(TOTAL Num(Month(Date#(MOIS,'MMMM'))))-1,
Count (TOTAL {<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'},[REG CODE NIV1 CIBPRIO1]={'OUI'}, DATE_JOUR={">=$(v12MoisDebut)<$(vAujourdhui)"},
//MOIS={">=$(=Max(MOIS-1))<=$(=Max(MOIS))"},
[REG LIB NIV2 PXI FAM COMPLET]=,
[REG LIB NIV2 SITE-INDUSTRIEL]=,
[REG LIB NIV3 PXI S-FAM COMPLET]=,
[REG LIB NIV1 TOP VENTE]=,
[REG LIB NIV2 TOP VENTE]=,
[REG LIB NIV1 RESEAU]=,
[REG LIB NIV2 TYPE POINT VENTE]=,
[REG LIB NIV3 SOUS-TYPE POINT VENTE]=,
[ARTICLE]= >} distinct %CLIENT_ID ),
Count ({<TYPE_CALENDRIER={'N'},ORIGIN={'FACTURES'},[REG CODE NIV1 CIBPRIO1]={'OUI'}, DATE_JOUR={">=$(v12MoisDebut)<$(vAujourdhui)"},
[REG LIB NIV2 PXI FAM COMPLET]=,
[REG LIB NIV2 SITE-INDUSTRIEL]=,
[REG LIB NIV3 PXI S-FAM COMPLET]=,
[REG LIB NIV1 TOP VENTE]=,
[REG LIB NIV2 TOP VENTE]=,
[REG LIB NIV1 RESEAU]=,
[REG LIB NIV2 TYPE POINT VENTE]=,
[REG LIB NIV3 SOUS-TYPE POINT VENTE]=,
[ARTICLE]= >} distinct %CLIENT_ID ))