topic Re: SET ANALYSIS AGGR in QlikView

SET ANALYSIS AGGR

souadouert — Thu, 25 Oct 2018 11:11:39 GMT

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)

Re: SET ANALYSIS AGGR

sunny_talwar — Thu, 25 Oct 2018 11:22:45 GMT

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?

Re: SET ANALYSIS AGGR

souadouert — Thu, 25 Oct 2018 12:55:31 GMT

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

Re: SET ANALYSIS AGGR

sunny_talwar — Thu, 25 Oct 2018 13:01:54 GMT

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

Re: SET ANALYSIS AGGR

souadouert — Thu, 25 Oct 2018 13:10:19 GMT

yes Sunny !!

Re: SET ANALYSIS AGGR

sunny_talwar — Thu, 25 Oct 2018 13:24:57 GMT

May be this

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

Re: SET ANALYSIS AGGR

souadouert — Thu, 25 Oct 2018 14:22:01 GMT

Thank you sunny