Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Need Help With Date Formula

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.

1 Solution

Accepted Solutions

Yes there should be

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

View solution in original post

17 Replies

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?

Creator III
Creator III

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

Partner
Partner

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

Partner
Partner

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)

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.

Partner
Partner

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.

Partner
Partner

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.

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)

Partner
Partner

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)

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