Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:  Contributor III

## Above Date Compare

I have a need to put a chart my dashboard that looks at dates in the above row and compare it to information in the current row

This is a basic view of my data:

ID, Name, Start Date, End Date
123, ABC DEF, 16/10/2017, 10/07/2018
234, ABC DEF, 13/04/2018,
124, GHJ KLM, 02/03/2018, 16/03/2018
237, GHJ KLM, 30/04/2018,
];

The first name has a start date which is before the end date of the prior row. I can get an expression to flag this row as being invalid but I need to flag both rows which is where I'm failing.

I'm using this formula:

If( Above( TOTAL [Name] ) = [Name],
If( [Start Date] - Above( TOTAL [End Date] ) < 0,
1,
0  ),
0 )

It flags my second row with a 1. I need a 1 to appear in BOTH rows so I can limit the table to anything over 0. I'm sure I need to use an aggr but I can't get the function to return any results. Even looked at RangeSum but again not getting what I need.

Labels (2)

• ### AGGR()

1 Solution

Accepted Solutions  MVP

May be this

``````If(
(Above(TOTAL [Name]) = [Name] and [Start Date] - Above(TOTAL [End Date]) < 0) or
(Below(TOTAL [Name]) = [Name] and Below(TOTAL [Start Date]) - [End Date] < 0)
, 1, 0)``````
2 Replies  MVP

May be this

``````If(
(Above(TOTAL [Name]) = [Name] and [Start Date] - Above(TOTAL [End Date]) < 0) or
(Below(TOTAL [Name]) = [Name] and Below(TOTAL [Start Date]) - [End Date] < 0)
, 1, 0)``````  Contributor III
Author

How silly of me to not think of 'BELOW' too. That's worked for me, thanks Sunny! Community Browser