Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
keithlawrence
Contributor III
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:

LOAD * INLINE [
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.

 

Anyone offer any advice?

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

2 Replies
sunny_talwar

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)

View solution in original post

keithlawrence
Contributor III
Contributor III
Author

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