Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
markramiro
Creator II
Creator II

Set Analysis and Aggr - not calculating correctly

I have two tables:

IDArea
123NORTH
345NORTH
567SOUTH
789SOUTH

IDActionDate
123Execute04/01/2050
123Plan09/01/2050
345Execute12/01/1999
567Plan12/01/1999
789Execute12/01/1999
789Execute06/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.

13 Replies
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))


Capture.PNG

hanstorsvik
Contributor II
Contributor II

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

Untitled.png

tunoi
Creator
Creator

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
Partner
Partner

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