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

[Anonymous]

it's perfect

Thanks a lot Sunny

[jonvitale]

I agree that it doesn't work (at least in Sense). I get the error "Nested aggregation is not allowed."

[jonvitale]

Looks like you have an answer that works, but here is another alternative.

You can do multiple levels of aggregation, but according to this thread nested aggregation not allowed the AGGR function and the aggregation functions need to be interspersed.

Here's a function that does this:

=
  Sum(
     Aggr(
          IF(Sum( Aggr(Count(distinct {$<Date = {">10/10/2016"}>} OrderNum), Client))=2, Sum(Amount)), 
          Client
    )
)

Basically, it uses your count function, discards any non-2 order counts, aggregates by client on the total amount, aggregates again on these values, and sums it all up. Pretty complex, but it's an interesting exercise in understanding how AGGR functions work.

I attached a sample qlik sense application that you can check out to verify that this works. Sorry, I don't have QlikView.

p.s., note to other posters - I know we are all busy - but can you please at least give a one or two sentence explanation for your answers? How are we supposed to learn how to do this ourselves if you don't explain your answer. Remember, math class? Show your work! (Coming from a former math teacher).

[sunny_talwar]

What do you not understand about the expression I posted?

Sum({<[Code Client] = {"=Count(DISTINCT {<[Date Commande]={"">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)""}>} [N° Cde]) = 2"}>} [Ca Produit HT])

Here I am doing a summation of [Ca Produit HT] where this condition met for Code Client -> Count(DISTINCT {<[Date Commande]={"">=$(=vDateDébutAnnéeN) <=$(=vDateFinSeg)""}>} [N° Cde]) = 2.

[jonvitale]

Well, specifically, on first look I was having trouble understanding how your double-quotation marks are functioning here. I suppose that the inner double-double marks "" serve as a sort of escape character.

I don't want to accuse you specifically - you are obviously trying to be helpful - but I think there is sometimes a culture of "look what I can do" on these forums. I.e., trying to prove that you can do what the OP couldn't do, really quickly. That's fine if the OP needs a fix right now, but many of us use these forums to actually learn. And in that case, the one line solution is not the best thing. I'm not accusing you, specifically of that... it's just been getting on my nerves.

[sunny_talwar]

jonvitale‌ I see what you are coming from, and not to lie, I am probably a culprit of this myself. I can't promise I will change, but I will try to do better. Having said that, if you ever come across a post by me which you don't understand, please don't hesitate to seek explanation

Also, you are right about the usage of 2 double quotes.

Escape sequences

Best,

Sunny

[sunny_talwar]

In general, I would suggest anyone to avoid Aggr() function if the Aggr() is done over a single dimension and use Set analysis search string option because of its efficiency benefit. A lot of the time we look for solutions and don't really care about efficiency, but I would suggest that if you have time, look for ways to make your expression better .

[Anonymous]

Hi,

It's  true it's very difficult to do whithout aggr.I tried to do the same thing with this formula to calculate the sum of the delays between the first order and the second order for all customers and divide by the same number of customers to get an average time. This formula works but is very greedy resource.Do you have an idea? thank you for your help and time

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

/

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

[sunny_talwar]

What is your chart dimension where you are using this? Or are you using this in a text box object?

[Anonymous]

in a text box object

thanks