topic Re: Help with aggr count function (with condition) in App Development

Help with aggr count function (with condition)

patricesalem — Fri, 15 Nov 2024 23:38:09 GMT

Hello

I have a detailed table with orders , line of orders, qty and delivery date (to summarize - the table is more complex) :

801 orders is : count(distinct COMMANDE)

Next to it, I have a table to summarize all shipment information :

Looking particularely at Late orders , I get 163 :
Late orders = count(distinct aggr(if(WEEK_SM_REELLE_ZSQ='NOT_DELIVERED_YET',COMMANDE,POSTE),YEAR_WEEK_REF_TAUX_SERVICE,COMMANDE))

WEEK_SM_REELLE_ZSQ=shipment week
COMMANDE=ORDER
POSTE=LINE
YEAR_WEEK_REF_TAUX_SERVICE = Planned shipment week/

When I filter the first detailed table on WEEK_SM_REELLE_ZSQ='NOT_DELIVERED_YET', I count 171 late orders

At the same time, my summary table is updated and the "late orders "figure moves from 163 to 171. I don't understand why.

Is my aggr formula wrong ?

Any idea how I could make my formula better ?

thanks

Re: Help with aggr count function (with condition)

patricesalem — Wed, 13 Oct 2021 06:24:49 GMT

hello again,

anybody could put me in the right on how to use correctly the distinct, aggr, count combination ?

I know that the positionning of the distinct syntax can have an impact on the results. I can't find the correct way.

I have exported my data to Excel and the correct number of order late I have to find is 171

Thanks for your help

Re: Help with aggr count function (with condition)

tresB — Wed, 13 Oct 2021 07:12:28 GMT

Could your share a sample excel explaining the expected out put in that context?

Re: Help with aggr count function (with condition)

patricesalem — Wed, 13 Oct 2021 08:57:00 GMT

Hello Tresesco

Thanks for your interest in my post !

I'm sending you the detailed order shipment porfolio of orders supposed to be sent on week 39 (Planned shipment week = 202139).

The excel file is an exportation of a Qlik straight table

The column Shipment week indicates the date of shipment. If the line is not shipped yet (on week 39) then 'NOT_DELIVERED_YET' is indicated

My need :
I would like to group in a second straight table the numbers of orders not shipped on week 39. If at least one line of the order is not shipped , then the order is counted as late :

171 orders

Note that an order can have lines shipped on time and line not shipped :

thanks for your help

Re: Help with aggr count function (with condition)

tresB — Wed, 13 Oct 2021 11:49:12 GMT

If you have those dimensions in the chart, you can avoid aggr() and try expression like:

=count(distinct {<[Shipment week]={'NOT_DELIVERED_YET'}>}[Nr Commande MX])

and if you have to use aggr, then include the shipment week also in dimension list in aggr, like:

count(distinct aggr(if(WEEK_SM_REELLE_ZSQ='NOT_DELIVERED_YET',COMMANDE,POSTE),YEAR_WEEK_REF_TAUX_SERVICE,WEEK_SM_REELLE_ZSQ,COMMANDE))

Re: Help with aggr count function (with condition)

patricesalem — Wed, 13 Oct 2021 12:30:12 GMT

Great it works like a charm, well done ! (I've used the first option...)

Maybe, could you help me with the use of your first syntax

I would like to count the number of orders that are shipped in advance (WEEK_SM_REELLE_ZSQ<YEAR_WEEK_REF_TAUX_SERVICE)

I tried :

=count (distinct {<WEEK_SM_REELLE_ZSQ={"<YEAR_WEEK_REF_TAUX_SERVICE"},POSTE_ON_TIME_ZSQ={'1'}>}COMMANDE)

it returns 0...

I have also tried different variations :

=count (distinct {<WEEK_SM_REELLE_ZSQ=YEAR_WEEK_REF_TAUX_SERVICE,POSTE_ON_TIME_ZSQ={'1'}>}COMMANDE)

=count (distinct {<WEEK_SM_REELLE_ZSQ={YEAR_WEEK_REF_TAUX_SERVICE},POSTE_ON_TIME_ZSQ={'1'}>}COMMANDE)

I've been using this syntax in the past (with another QVF) but instead of having a field name (YEAR_WEEK_REF_TAUX_SERVICE), I was using a variable and could use < or > operators :

sum({<[D_SALES.autoCalendar.#Week]={"<$(VCurrentWeek)"}-1,[Family code]-={'60'},[HIERARCHIE]-={"PIECE*"}>}QTE_POSTE)

Any idea

BIG THANK AGAIN

Re: Help with aggr count function (with condition)

patricesalem — Wed, 13 Oct 2021 13:01:53 GMT

I found the solution using this blog post

=count (distinct {<[COMMANDE]={"=WEEK_SM_REELLE_ZSQ<YEAR_WEEK_REF_TAUX_SERVICE"}>}COMMANDE)

And to go further, I able to count the number of orders shipped in advance complete (all requested qty shipped)

=count (distinct {<[COMMANDE]={"=WEEK_SM_REELLE_ZSQ<YEAR_WEEK_REF_TAUX_SERVICE and sum(QTE_POSTE)=sum(QTE_EXPEDIEE_ZSQ)"}>}COMMANDE)

Thanks again ..I've been looking for a solution for hours...your input opened new doors for me

Re: Help with aggr count function (with condition)

patricesalem — Wed, 13 Oct 2021 14:04:29 GMT

Tresesco, sorry to bother you again but Qlik drives me crazy with these expressions.

I'm trying to calculate something very simple : the number of orders shipped on week 39.

If I use your expression in a "static way" to check the value I have to found :

=count(distinct {<[Shipment week]={'202139'}>}[Nr Commande MX])
It returns 406 -> this is correct.

If I use the other method I found to be able to make the calculation automatically in my straight table :
=count (distinct {<[COMMANDE]={"=WEEK_SM_REELLE_ZSQ=YEAR_WEEK_REF_TAUX_SERVICE"}>}COMMANDE)

Then I get :

247, I don't undertand why I get this result...

Even worse, if I click on 202139, then I get the another figure, closer to the 406 :

It has been driving me nuts for hours if not days that I'm facing exactly the same issue...as soon as I select a planned shipement week in my straight table, the displayed result is not the same as previously displayed when no selection is made.

For this particular example, are you able to explain me why ?

THANKS

Re: Help with aggr count function (with condition)

tresB — Thu, 14 Oct 2021 05:40:06 GMT

Don't worry, set analysis or qlik for that matter drives most of us crazy at some point of time because it has huge potential and flexibility leading to also bring complexity with it. 😛 But good thing is that once you are close to mastering it - you are far from being crazy as well; so with time - you will champion it some day.

The section - [COMMANDE]={"=WEEK_SM_REELLE_ZSQ=YEAR_WEEK_REF_TAUX_SERVICE"} of your expression is little tricky; it works as expected depending on couple of things. I am not going much deep into it here. Instead, let me ask you your expected output so that I can try couple of things and validate based on your already shared data.

Re: Help with aggr count function (with condition)

patricesalem — Thu, 14 Oct 2021 12:23:41 GMT

Tresesco

thanks for your help. To avoid mixing two questions, I have opened another question...

https://community.qlik.com/t5/App-Development/Count-distinct-not-working-as-expected-in-set-expression/m-p/1846691#M70023