Need Help With Date Formula

[pnn44794]

Hello:

I have a straight table where I am trying to show items that have been Approved Offline in the last seven days, with those seven days being Tuesday to Tuesday (i.e. 1/3/2017 to 1/10/2017).  I'm trying the following formula but nothing is returned though I know I have items that should be.

Status='Approved - Offline' and [Approval Date] <= date(max([Approval Date])-7)

Approval Date is in the format of mm-dd-yyyy.  I suspect I'm missing something fairly simple with this.

As always, any and all help is appreciated.  Thanks in advance.

[pnn44794]

Correct and it highlights the first ( right after Avg.

[sunny_talwar]

I have no idea.... If you are able to share a sample, I can take a look

[pnn44794]

I also noticed there is only one " (double quote) before the dollar sign and no matching one after.  Should there be?

[pnn44794]

It's both expressions you suggested that have the issue.  There's 3 ('s and 4 )'s which I believe is causing the issue.  Also, I noticed there's only one " (double quote) in front of the only dollar sign in the expressions.  Should there be two matching double quotes?

[sunny_talwar · Accepted solution]

Yes there should be

Avg({<Status = {'Approved - Offline'}, [Approval Date] = {"$(='<=' & Date(Max({1}[Approval Date])-7, 'MM/DD/YYYY'))"}>} 1)

[pnn44794]

A big thanks once again, Sunny.  Both expressions work.  However, I needed to change '<=' to '>=' because <= was returning everything with Approved - Offline 7 days or older and I wanted seven days or newer.  All good no though.

One last question before I mark your answer as the correct one.  Can you explain what the formula is doing?  For example, why use Avg, what does the $ do, what's the 1 for?  Sorry.  Still trying to grasp Qlikview's functions and syntax.

[sunny_talwar]

I used Avg()  because I wanted to use set analysis the goal here was to show only those rows where you set analysis met the condition.

$() is used for dollar sign expansion. Read about all this here:

A Primer on Set Analysis

The Magic of Dollar Expansions

[pnn44794]

Thanks again Sunny.  Much appreciated.