topic Re: Customized Total Expression in QlikView

Customized Total Expression

Anonymous — Tue, 10 Apr 2018 13:39:49 GMT

Hello,

I would like to customize the total expression in a multi dimensional table in order to obtain something like the picture below.

Let's focus now on my real table below :

I have worked on the code below for the column TEST :

if( Dimensionality()=0

, count(

aggr(

if([Taux Non Conformes] >= 0.25, 1)

, depot, Année, TrimDate, Mois_Ecrit_En_Entier

)

, [Taux Non Conformes]

)

So, I want to get the number of lines that are >= 25% but the total always show 0.0.

Any idea ?

Re: Customized Total Expression

sunny_talwar — Tue, 10 Apr 2018 15:26:01 GMT

Is [Taux Non Conformes] a field name or is this column label in the chart? If this is a column label, then it won't work within the Aggr() function... in order to use this within the Aggr() function, you will have to use the actual variable behind [Taux Non Conformes]

Re: Customized Total Expression

sunny_talwar — Tue, 10 Apr 2018 15:26:45 GMT

Replace [Taux Non Conformes] with the actual expression.....

if( Dimensionality()=0

, count(

aggr(

if([Taux Non Conformes] >= 0.25, 1)

, depot, Année, TrimDate, Mois_Ecrit_En_Entier

)

, [Taux Non Conformes]

)

Re: Customized Total Expression

Anonymous — Wed, 11 Apr 2018 07:19:33 GMT

Ok, I changed the expression for [Taux Non Conformes].

if( Dimensionality()=0

, count(

aggr(

if(((count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage)) >= 0.25, 1)

, depot, Année, TrimDate, Mois_Ecrit_En_Entier

)

, (count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage)

)

I am supposed to obtain the total number of lines >= 0.25 but the total is still showing 0,0.

Re: Customized Total Expression

sunny_talwar — Wed, 11 Apr 2018 08:54:31 GMT

How about if you do this?

If(Dimensionality()=0

, count(TOTAL

aggr(

if(((count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage)) >= 0.25, 1)

, depot, Année, TrimDate, Mois_Ecrit_En_Entier

)

, (count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage)

)

Re: Customized Total Expression

Anonymous — Wed, 11 Apr 2018 12:25:38 GMT

It doesn't work. Still 0,0 displayed.

Re: Customized Total Expression

sunny_talwar — Wed, 11 Apr 2018 12:26:43 GMT

Would you be able to share a sample to check this out?

Re: Customized Total Expression

Anonymous — Thu, 26 Apr 2018 08:19:59 GMT

Hi,

Here is the solution to my problem :

=if( Dimensionality()=0

, num(count(distinct {< Depot={"=((count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage))>0.25"} >} Depot),'0')

, num((count(if(Dérogation = 'Avec Dérogation', 1)) + count(if(Statut = 'Refusé', 1))) / count(N°Echantillonage),'0.0%')

)

Table properties => numbers => expression "Taux Non Conformes" => default format

Expression "Taux Non Conformes" :

use num() to display data as % and total as integer.
use a set analysis on the function count(distinct Depot) to count only depots with taux > 25%.

NOTA :

This set analysis syntax is quite tricky compared to the classic one because you loose the syntaxic analyser.

Another example to illustrate the problem :

We want to count the number of clients having a CA > 1 million.

We use Count(Distinct ClientId).

We put a set analysis on the function to calculate the CA > 1 million :

Count(distinct {< ClientId = {‘’ =sum(CA)>1000000 ’’} >} ClientId)

=> Yata !