topic Re: IF AGGR COUNT in QlikView

IF AGGR COUNT

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: IF AGGR COUNT

ali_hijazi — Mon, 20 Nov 2017 13:23:33 GMT

sum(aggr(count(distinct.....),[code client]))

Re: IF AGGR COUNT

sunny_talwar — Mon, 20 Nov 2017 13:28:41 GMT

Are you using this as your calculated dimension or is this an expression?

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

Re: IF AGGR COUNT

YoussefBelloum — Mon, 20 Nov 2017 13:38:15 GMT

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

,sum([Ca Produit HT]))

Re: IF AGGR COUNT

Anonymous — Mon, 20 Nov 2017 13:42:36 GMT

this is an expression

thanks

Re: IF AGGR COUNT

Anonymous — Mon, 20 Nov 2017 13:49:41 GMT

thanks but how to get afterwards only the sum of all this in a single expression?

Re: IF AGGR COUNT

stabben23 — Mon, 20 Nov 2017 13:56:00 GMT

then you need to "wrap" a sum around the Expression.

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

,([Ca Produit HT])))

Re: IF AGGR COUNT

Anonymous — Mon, 20 Nov 2017 14:03:41 GMT

sorry it doesn't work

Re: IF AGGR COUNT

YoussefBelloum — Mon, 20 Nov 2017 14:07:57 GMT

here it is a single expression which will give the result you want, did you try it ?

Re: IF AGGR COUNT

sunny_talwar — Mon, 20 Nov 2017 14:12:17 GMT

May be this

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

Re: IF AGGR COUNT

Anonymous — Mon, 20 Nov 2017 14:53:07 GMT

it's perfect

Thanks a lot Sunny

Re: IF AGGR COUNT

jonvitale — Mon, 20 Nov 2017 15:00:34 GMT

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

Re: IF AGGR COUNT

jonvitale — Mon, 20 Nov 2017 17:44:26 GMT

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

Re: IF AGGR COUNT

sunny_talwar — Mon, 20 Nov 2017 17:53:59 GMT

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.

Re: IF AGGR COUNT

jonvitale — Mon, 20 Nov 2017 18:04:18 GMT

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.

Re: IF AGGR COUNT

sunny_talwar — Mon, 20 Nov 2017 18:11:08 GMT

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

Re: IF AGGR COUNT

sunny_talwar — Mon, 20 Nov 2017 18:14:28 GMT

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 .

Re: IF AGGR COUNT

Anonymous — Tue, 21 Nov 2017 08:31:45 GMT

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

Re: IF AGGR COUNT

sunny_talwar — Tue, 21 Nov 2017 09:34:37 GMT

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

Re: IF AGGR COUNT

Anonymous — Tue, 21 Nov 2017 09:55:40 GMT

in a text box object

thanks