Announcements
cancel
Showing results for
Did you mean:
Creator

## Total count of rows - what expression ?

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

Best regards

1 Solution

Accepted Solutions
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
Master

Partner - Creator II

Hi,

Maybe something like this:

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

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

-Teemu

Creator
Author

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)

Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Creator II

=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

Creator
Author