Set Analysis and Aggr - not calculating correctly

[Anonymous]

I have two tables:

ID	Area
123	NORTH
345	NORTH
567	SOUTH
789	SOUTH

ID	Action	Date
123	Execute	04/01/2050
123	Plan	09/01/2050
345	Execute	12/01/1999
567	Plan	12/01/1999
789	Execute	12/01/1999
789	Execute	06/01/2015

I then created a Straight Table where Dimension is [Area] and there are three expressions. Please see attached file.

1st expression ("With Execute"): Count all ID's that have an "Execute" Action with a Date >= Today

=Count(if(len(aggr(min({<[Date]={">=$(=today())"},[Action]={"Execute"}>}[Date]),[ID]))>0,[ID]))

2nd expression ("With Plan"): Count all ID's that have a "Plan" Action with a Date >= Today

=Count(if(len(aggr(min({<[Date]={">=$(=today())"},[Action]={"Plan"}>}[Date]),[ID]))>0,[ID]))

1st Problem:

3rd expression ("Without Execute/Plan"): Count all ID's that do not have either an "Execute" or "Plan" Action with a Date >= Today

This one above I haven't figured out what is wrong as it is not calculating correctly. If ID=123, this count should be zero. If ID=789, this count should be zero as well.

=Count(if(isnull(aggr(min({<[Date]={">=$(=today())"},[Action]={"Execute","Plan"}>}[Date]),[ID])),[ID]))

2nd Problem: The first two expressions above ("With Execute") and ("With Plan") are working fine. But I realize I have records that have both an "Execute" and a "Plan" Action with Date greater than Today. And I don't want them to double count. For example, if you select ID=123, it will currently give a count of 1 on both expressions. I only need it to say 1 for "With Execute" and 0 for "With Plan". Reason being that I don't want it to be counted anymore as a "With Plan" because it already has an "Execute" Action at an earlier date. In other words, if the ID has both an "Execute" and a "Plan" Action with Date greater than today's date, it should only look at the earliest between the two.

[sunny_talwar]

Not 100% sure if it meets all the requirements, but for the sample it seems to work

Count(DISTINCT {<ID = e({<[Date]={">=$(=today())"}>})>}Aggr(If(SubStringCount(Concat(DISTINCT '|'&Action&'|'), '|Plan|') and SubStringCount(Concat(DISTINCT '|'&Action&'|'), '|Execute|'), Null(), ID), ID, Area))

[hanstorsvik · Accepted solution]

There's no end to the possibilities... 🙂

Yes it is possible...

You can use the Firstordervalue, with rows with date over todays date, to look for action.

I.e.:

=count(distinct aggr(if(FirstSortedValue( {$<Date={">$(=today())"}>} distinct total <Area,ID> Action,Date)='Execute',ID),Area,ID))

=count(distinct aggr(if(FirstSortedValue( {$<Date={">$(=today())"}>} distinct total <Area,ID> Action,Date)='Plan',ID),Area,ID))

=Count(DISTINCT {$-$<Date = {'>=$(=Date(Today()))'} ,Action={"Plan","Execute"}>} ID)

(Don't show null values)

BR

Hans

[tunoi]

For the 3rd expression i would try to use:

Count({<ID = E({<Action = {'Execute','Plan'}, DATE = {'>=$(=Date(Today()))'} >})>}Distinct [ID])

for me seems to work with the sample you provided

[teempi]

Hi,

Try these:

With Execute: Count(If(Match(Aggr(FirstSortedValue( {< Date={">=$(=Today())"} >} Action, Date), ID), 'Execute') > 0, ID))

With Plan: Count(If(Match(Aggr(FirstSortedValue( {< Date={">=$(=Today())"} >} Action, Date), ID), 'Plan') > 0, ID))

Without: Count(DISTINCT ID) - Count(If(Match(Aggr(FirstSortedValue( {< Date={">=$(=Today())"} >} Action, Date), ID), 'Execute', 'Plan') > 0, ID))

edit: some formatting

edit2: changed to get the earliest value instead of latest

-Teemu