IF AGGR COUNT

[Anonymous]

Hello, I would like to have the turnover of customers having 2 orders over a period.

But my formula does not work.

My order count is good

Nb of order : count( DISTINCT{<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [N° Cde])

but not formula  :

Turnerover for 2 orders= If(   Aggr(count( DISTINCT{<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [N° Cde]), [Code Client])=2

,[Ca Produit HT])

I need to aggr because in the end i need only one figure that will be the sum of all these clients

thanks for your help

[sunny_talwar]

From what I am understanding, the denominator is just being used to make sure that the result is shown if number of days are greater than 0? Is that right? But I don't see how you can have a negative number, and 0 should not be a problem anyway... so why not just use this? or am I missing something here?

Sum(Aggr(Interval(min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],2)-min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],1),'D'),[Code Client]))

[Anonymous]

actually the denominator allows me to get the number of client for which the delay between the first is not zero. I added a negative sign because the sum of the aggr function was negative

-120 174= sum(   aggr(  Interval(  min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],2)-min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],1),'D')>=1,[Code Client]))

OtherwiseI get the same result using the avg function

=Avg(aggr(Interval(  min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],2)-min(  {<[Date Commande]={">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)"}>} [Date Commande],1),'D'),[Code Client]))

but I still have aggr function. Is it possible to use another function  or syntax more efficient?

thanks a lot

[sunny_talwar]

For something like this, I don't think you would be able to avoid the usage of Aggr() function

[Anonymous]

ok thanks for your time it's kind of you

[Anonymous]

Hello Sunny,

I'm sorry to disturb you again  but I have something weird when I make the following calculation

55 984=Count(DISTINCT {<[Code Client] = {"=Count(DISTINCT {<[Date Commande]={"">=$(=vDateDébutAnnéePrec) <=$(=vDate12moisF)""}>}[N° Cde])>1"}>}    [Code Client])

-133 901 =Count(DISTINCT {<[Date Commande]={">=$(=vDateDébutAnnéePrec) <=$(=vDate12moisF)"}>} [N° Cde]) > 1

normally should I have the same number?

thnaks

[sunny_talwar]

Where are you using the second expression? In a chart? What is the dimension? Also, is there a one-to-one relationship between N° Cde AND Code Client?

[Anonymous]

Hello,

I found by changing the formula and going through a sum it works

Thank you sunny for your time