topic Getting the count of a filter from an if statement in App Development

Getting the count of a filter from an if statement

Evan0211 — Fri, 10 Mar 2023 16:20:19 GMT

I have a set of data that I am using to build a table. In that table, there are initial dates and completed dates. I have written an if statement to assign a new column to the table with a label that corresponds to the dates to state if it is on time or late

ex: If(not isnull([Revised Date]) and (Date([Revised Date]) < Today() and isnull([Completed Date])), 'Late'

This works as intended. Now I need to display a count of the items that are late and a count of the items that are on time in a KPI. How do I count all of the conditions that fall within each if statement?

Re: Getting the count of a filter from an if statement

BrunPierre — Sat, 11 Mar 2023 07:52:02 GMT

Hi, Using a Count If()

=Count(DISTINCT If(not IsNull([Revised Date]) and (Date([Revised Date]) < Today() and IsNull([Completed Date])), Items))

Set Analysis

=Count({<[Revised Date]={"<$(=Date(Today()))"},[Completed Date]={"=Len(Trim([Completed Date]))=0"},[Revised Date]={"=Len(Trim([Revised Date]))<>0"}>} DISTINCT Items)

Re: Getting the count of a filter from an if statement

barnabyd — Sun, 12 Mar 2023 20:19:36 GMT

G'day @Evan0211,

@BrunPierre's solution is correct but I would like to suggest an improvement. It is more efficient to if you create your extra column in the load script and the use that column in the Set Analysis.

The most efficient is to create a flag like this ...

If( not isnull([Revised Date]) and (Date([Revised Date]) < Today() and isnull([Completed Date])), 1, 0 ) as isLate

Then your Set Analysis looks like this ...

count( {$<isLate={1}>} distinct Item )

I hope this helps.

Cheers, Barnaby.