topic Re: Set Analysis and Aggr - not calculating correctly in QlikView

Set Analysis and Aggr - not calculating correctly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set Analysis and Aggr - not calculating correctly

devarasu07 — Thu, 08 Feb 2018 08:13:00 GMT

Hi,

you can try like this

With Execute

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

With Plan

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

Without Execute/Plan

=Count(DISTINCT {$<DATE = {'>=$(=Date(Today()))'} ,Action={"=len(trim(Action))=0"}>} ID)

Thanks,
Deva

Re: Set Analysis and Aggr - not calculating correctly

ali_hijazi — Thu, 08 Feb 2018 08:30:01 GMT

with Execute: count(distinct{<Action={"Execute"},[Date]={">=$(=today())"}>}ID)

with Plan: =Count(distinct{<[Date]={">=$(=today())"},[Action]={"Plan"}>}[ID])

3rd expression: =Count(distinct{$-{<[Date]={">=$(=today())"},[Action]={"Execute","Plan"}>}[ID])

Re: Set Analysis and Aggr - not calculating correctly

Anonymous — Thu, 08 Feb 2018 09:07:47 GMT

Hi, the changes to the first two expressions are giving the same output as my original expressions. Does not seem to make any difference.

For the 3rd expression, it is now showing zeros. There should be a count of 1 for NORTH (because ID=345 falls under this condition being that it does not have either an "Execute" or "Plan" Action with Date>=Today). And there should be a count of 1 for SOUTH (because ID=567 falls under this condition being that it does not have either an "Execute" or "Plan" Action with Date>=Today).

Re: Set Analysis and Aggr - not calculating correctly

Anonymous — Thu, 08 Feb 2018 09:10:28 GMT

Gives the same results as my original expressions.

For 3rd expression ("Without Execute/Plan"), there should be a count of 1 for NORTH (because ID=345 falls under this condition being that it does not have either an "Execute" or "Plan" Action with Date>=Today). And there should be a count of 1 for SOUTH (because ID=567 falls under this condition being that it does not have either an "Execute" or "Plan" Action with Date>=Today).

Re: Set Analysis and Aggr - not calculating correctly

lorenzoconforti — Thu, 08 Feb 2018 09:44:58 GMT

See attached, building on Deva's file

Re: Set Analysis and Aggr - not calculating correctly

Anonymous — Thu, 08 Feb 2018 10:35:38 GMT

I may not have been very clear with my original post. I have simplified my example in the revised attached.

My 1st and 2nd expressions are working just fine. The only thing I was exploring was whether I can avoid double counting such that if a record has both a "Plan" and "Execute" Action, I only want it to count under either "With Execute" or "With Plan" (not both). Example is ID=123. It has both a "Plan" and a "Execute" [Action] but I only want it to be counted under "With Plan" because between the two, "Plan" has the earliest [Date]. So if ID=123 is selected, "With Execute" should be 0 and "With Plan" should be 1.

The other problem I have is that my 3rd expression is not calculating correctly. It should only count if a record has neither an "Execute" nor a "Plan" Action (examples are ID=345 and ID=567). At the moment also ID=123 and ID=789 are being counted in this column when they shouldn't.

DESIRED:

Put simply, each record should only be counted under one of the categories i.e. With Execute, With Plan, or Without Execute/Plan. Without any selections, in the chart, NORTH should say 0 for "With Execute", 1 for "With Plan" (ID=123), and 1 for "Without Execute/Plan" (ID=345). SOUTH should say 1 for "With Execute" (ID=789), 0 for "With Plan", and 1 for "Without Execute/Plan" (ID=567).

Re: Set Analysis and Aggr - not calculating correctly

lorenzoconforti — Thu, 08 Feb 2018 10:42:07 GMT

Hi,

Have you had a look at the application I've posted before?

To me it's doing what you are asking

You only have two IDs with Date higher than today (123 and 789)

Both are showing "with execute"; none showing "With plan" (123 has a plan action but, since it also has an execute, it is disregarded)

None are showing without execute and plan as it should be based on your description

Re: Set Analysis and Aggr - not calculating correctly

Anonymous — Thu, 08 Feb 2018 10:56:39 GMT

Hi Lorenzo,

Yes saw your file earlier, thanks. For 123 I'd actually like it to fall under "With Plan". Essentially if a record has both a "Plan" and an "Execute", I'd like it to be counted where the Date is earliest. So for 123 this is Plan because 4/1/2050 < 9/1/2050.

Then for "Without Execute/Plan", 345 and 567 should be counted here because neither of them have either an "Execute" or a "Plan" that has a Date >= Today. They both have a "Execute" and a "Plan" Action but their Dates are in 1999 which is less than Today's date. I understand my initial code is definitely wrong for the 3rd expression, but basically the intended design is to count those records that do not have a "Plan" or "Execute" with a Date>=Today.

Put simply, each record should be counted under one of the categories i.e. With Execute, With Plan, or Without Execute/Plan.

Is this possible?

Re: Set Analysis and Aggr - not calculating correctly

lorenzoconforti — Thu, 08 Feb 2018 12:22:07 GMT

Hi Mark

I think I understand now. Not sure how to implement it; I've tried with FirstSortedValue and Aggr but cannot get to the desired result.

If I were you I would work on manipulating the data in the loading script. Create a summary table where you only keep the most recent action for each ID. I think it should then be then much easier to obtain what you are looking for

Can you please share the file TestData.xlsx?

Re: Set Analysis and Aggr - not calculating correctly

sunny_talwar — Thu, 08 Feb 2018 12:37:32 GMT

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

Re: Set Analysis and Aggr - not calculating correctly

hanstorsvik — Thu, 08 Feb 2018 12:51:33 GMT

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)

Hans

Re: Set Analysis and Aggr - not calculating correctly

tunoi — Thu, 08 Feb 2018 13:24:39 GMT

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

Re: Set Analysis and Aggr - not calculating correctly

teempi — Thu, 08 Feb 2018 13:41:21 GMT

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