Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
May be this
Count(DISTINCT {<Customer = {"=Index(Concat(DISTINCT {<YEAR= {$(=(YEAR_CURRENT))}>} '|' & Status & '|'), '|ok|') and FirstSortedValue(NB_articl+nb_PRODUCT, -Date) > 0"}>} Customer)
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?
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
So, basically if customer is has status = 'ok' for any date in the $YEAR and the latest date's value > 0, we count them?
yes Sunny !!
May be this
Count(DISTINCT {<Customer = {"=Index(Concat(DISTINCT {<YEAR= {$(=(YEAR_CURRENT))}>} '|' & Status & '|'), '|ok|') and FirstSortedValue(NB_articl+nb_PRODUCT, -Date) > 0"}>} Customer)
Thank you sunny