Total count of rows - what expression ?

[Hakim-A]

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

[shiveshsingh]

Please share your app

[teempi]

Hi,

Maybe something like this:

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

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

-Teemu

[Hakim-A]

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)

[jonathandienst]

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

[jonathandienst · Accepted solution]

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.

[teempi]

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

[Hakim-A]

I appreciate your reactivity guys Thanks for your help