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: 
souadouert
Specialist
Specialist

SET ANALYSIS AGGR

Hello

I need to establish the expression set analysis of these conditions

count -> Number of customer

NB_PRODUCT +NB_ARTICL > 0 in DATE=$VAR_DATE

and

in current year -> YEAR=$YEAR_CURRENT , STATUT=' ok'  because a customer can have several status in different date, just find a date with the situation 'ok'

So my expression

count(

if(sum(Aggr(sum( {< DATE={$(VAR_DATE)} ,STATUT={$(=Max({<YEAR= {$(=(YEAR_CURRENT ))},STATUT={'OK'}   }>} NB_PRODUCT +NB_ARTICL ),CUSTOMER_ID))>0,CUSTOMER_ID)

1 Solution

Accepted Solutions
sunny_talwar

May be this

Count(DISTINCT {<Customer =  {"=Index(Concat(DISTINCT {<YEAR= {$(=(YEAR_CURRENT))}>} '|' & Status & '|'), '|ok|') and FirstSortedValue(NB_articl+nb_PRODUCT, -Date) > 0"}>} Customer)

View solution in original post

6 Replies
sunny_talwar

Where are you planning to use this Souad? Also, can you share few rows of mocked up data to show what you have and what you want from it?

souadouert
Specialist
Specialist
Author

Capture.PNG

CUSTOMER 200 in last date 201803 the statut ='nok' but in currect year 201801 the statut='ok'  and NB_articl+nb_PRODUCT=19 in last date  So i count this customer

CUSTOMER 100 in last date 201803 the statut ='nok' but in currect year 201801 the statut='ok'  and NB_articl+nb_PRODUCT=0 in last date  So i dont count this customer

CUSTOMER 300 in currect year 201801 the statut='ok'  and NB_articl+nb_PRODUCT=11  So i count this customer

sunny_talwar

So, basically if customer is has status = 'ok' for any date in the $YEAR and the latest date's value > 0, we count them?

souadouert
Specialist
Specialist
Author

yes Sunny !!

sunny_talwar

May be this

Count(DISTINCT {<Customer =  {"=Index(Concat(DISTINCT {<YEAR= {$(=(YEAR_CURRENT))}>} '|' & Status & '|'), '|ok|') and FirstSortedValue(NB_articl+nb_PRODUCT, -Date) > 0"}>} Customer)

souadouert
Specialist
Specialist
Author

Thank you sunny