Above Date Compare

keithlawrence — Sat, 16 Nov 2024 02:34:29 GMT

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?

Re: Above Date Compare

sunny_talwar — Wed, 04 Sep 2019 12:27:36 GMT

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)

Re: Above Date Compare

keithlawrence — Wed, 04 Sep 2019 13:34:34 GMT

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

topic Re: Above Date Compare in QlikView

Above Date Compare

Re: Above Date Compare

Re: Above Date Compare