topic Re: Total count of rows - what expression ? in QlikView

Total count of rows - what expression ?

Hakim-A — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

Question : when creating a straight table it is possible to display the total count/sum of rows.

Let's say my table is showing VehicleID

and using expression like

=if(count(distinct [Job Status])=1, if([Job Status]='COMPLETED',1))

I have a list of Vehicles having only 1 job status that is completed.

Total can be seen at the top of bottom of straight table.

But what if I want this total count/sum of rows as a single expression (that I can re-use in a text object for example).

The idea is to calculate the number of vehicles having only 1 job status that was completed.

Job status can have different values : completed, failed, not existing, to be done

Thanks for your help !

Best regards

Re: Total count of rows - what expression ?

shiveshsingh — Mon, 19 Mar 2018 09:37:58 GMT

Please share your app

Re: Total count of rows - what expression ?

teempi — Mon, 19 Mar 2018 09:47:39 GMT

Hi,

Maybe something like this:

=count( {< [Job Status]={'COMPLETED'} >} DISTINCT VehicleID)

Replace VehicleID with whatever the field is called in your dataset.

-Teemu

Re: Total count of rows - what expression ?

Hakim-A — Mon, 19 Mar 2018 09:51:28 GMT

This calculation is not working because I have to make sure there is only 1 and one unique job status (some vehicles can have 3 different status, but I want to catch the one having 1 status that is COMPLETED)

Re: Total count of rows - what expression ?

jonathandienst — Mon, 19 Mar 2018 09:53:38 GMT

Something like this in Sum(If()) form:

=Sum(Aggr(If(Count(Distinct [Job Status]) = 1 And [Job Status] = 'COMPLETED', 1), VehicleID))

Where VehicleID is some unique identifier for the vehicles.

If you provide a sample document, then iy would be possible to convert this to a set expression (which will perform better if your data set is large).

Re: Total count of rows - what expression ?

jonathandienst — Mon, 19 Mar 2018 09:58:53 GMT

I think this will be sufficient

=Sum(Aggr(If([Job Status] = 'COMPLETED', 1), VehicleID))

If more than one job type exists for the vehicle, the condition will fail anyway.

Re: Total count of rows - what expression ?

teempi — Mon, 19 Mar 2018 10:00:42 GMT

Ok, how about this:

=count( {< VehicleID={"=Count([Job Status]) = 1 AND Count({< [Job Status]={'COMPLETED'} >} [Job Status]) = 1"} >} DISTINCT VehicleID)

The expressions checks that there is only one Job Status and that there is only one status with COMPLETED.

-Teemu

Re: Total count of rows - what expression ?

Hakim-A — Mon, 19 Mar 2018 10:03:40 GMT

I appreciate your reactivity guys Thanks for your help