topic Re: Need Help With Date Formula in QlikView

Need Help With Date Formula

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

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.

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 03:06:07 GMT

What object are you looking to do this in? Text box object? straight table? If it is straight table, then what are you dimensions? Do you have an existing expression?

Re: Need Help With Date Formula

satheshreddy — Tue, 10 Jan 2017 03:16:53 GMT

Hi Perry,

can you post dimensions for understanding, better to try below Exp.

if([Approval Date] <= date(max([Approval Date])-7),(Status='Approved - Offline'),'No Approvals')

Regards

Sathish

Re: Need Help With Date Formula

nagarjuna_kotha — Tue, 10 Jan 2017 04:18:29 GMT

Hi Try this :

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

Also if u have any issue check attched file.

-Nagarjun

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 16:24:18 GMT

Hello Sunny:

Sorry for the delay in replying. This is for a straight table. The Dimensions are Status and Approval Date. The existing expression, which does not return anything, is as follows:

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 16:26:44 GMT

Actually, I should probably also add that there are other dimensions that are a part of the table and which would then be displayed, but the two dimensions I gave are the two key dimensions for the expression.

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 16:29:22 GMT

Hello Sathish:

Sorry for the delay in replying. This is the same response as I gave Sunny.

This is for a straight table. The Dimensions are Status and Approval Date. The existing expression, which does not return anything, is as follows:

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

What I'm looking for is when I do a reload of the data, I want the straight table populated with any items that have Status of "Approved - Offline" (without the quotes) and which have had their status set to "Approved - Offline" within the last seven days. Otherwise, don't populate the straight table with anything. There are other dimensions that is Status = Approved - Offline and within the last 7 days based on Approval Date, that would be displayed. Status and Approval Date I believe are the key fields for the expression.

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 16:46:29 GMT

May be use this as your expression:

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

or if you want to see this from today's date:

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 17:21:06 GMT

Hello Sunny:

Thanks for the reply. Both expressions have a mismatched (. The exact error given is Token Doesn't Match.

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

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 17:29:48 GMT

Token doesn't match? That's the error you got?

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 17:31:32 GMT

Correct and it highlights the first ( right after Avg.

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 17:43:52 GMT

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 17:45:19 GMT

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 17:50:57 GMT

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?

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 17:52:57 GMT

Yes there should be

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 18:27:40 GMT

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.

Re: Need Help With Date Formula

sunny_talwar — Tue, 10 Jan 2017 18:42:13 GMT

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

Re: Need Help With Date Formula

pnn44794 — Tue, 10 Jan 2017 19:01:42 GMT

Thanks again Sunny. Much appreciated.