Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
leenlart
Creator
Creator

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

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

image.png

Thanks for any ideas!!

Labels (2)
5 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
leenlart
Creator
Creator
Author

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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
leenlart
Creator
Creator
Author

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

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 ))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)