topic Re: Before a date in QlikView

Before a date

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

Attached i have a snap shot of a straight table. I am trying to eliminate any date before the date Today - 2 days (Two Days Ago).

Below is the expression that gave me the snap shot of the straight table.

Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"}, project_name-={"PMO IMS Pipeline","PMO Test"}>}issue_due_date)

I need to add something like "issue_due_date >= today()-2" somewhere into equation above.

Can anyone tell me how I can get this ("issue_due_date >= today()-2") into the above equation?

Re: Before a date

vishsaggi — Tue, 14 Nov 2017 20:38:24 GMT

Try this?

= Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"},

project_name-={"PMO IMS Pipeline","PMO Test"},

issue_due_date = {"$(= '>='&Date(Today()-2, 'MM/DD/YYYY hh:mm:ss fff')) "} >}issue_due_date)

Re: Before a date

mparker123 — Tue, 14 Nov 2017 20:49:13 GMT

I tried it and it doesn't work my straight table displays nothing when this is added.

Re: Before a date

trdandamudi — Tue, 14 Nov 2017 20:52:33 GMT

May be as below:

Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"}, project_name-={"PMO IMS Pipeline","PMO Test"},issue_due_date={"$(= '>=' &Date(floor(Issue_Due_Date)-2)) "}>} issue_due_date)

Re: Before a date

vishsaggi — Tue, 14 Nov 2017 20:54:23 GMT

May be this?

= Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"},

project_name-={"PMO IMS Pipeline","PMO Test"},

issue_due_date = {"$(= '>='&Date(Max(issue_due_date)-2, 'MM/DD/YYYY hh:mm:ss fff')) "} >}issue_due_date)

Re: Before a date

mparker123 — Tue, 14 Nov 2017 20:55:58 GMT

did not work displaying nothing.

Re: Before a date

mparker123 — Tue, 14 Nov 2017 20:56:07 GMT

No this did not work either

Re: Before a date

vishsaggi — Tue, 14 Nov 2017 21:10:29 GMT

Can you share a sample to look into? And your expected output?

Re: Before a date

trdandamudi — Tue, 14 Nov 2017 21:11:32 GMT

Can you give a try on the below:

Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"}, project_name-={"PMO IMS Pipeline","PMO Test"},issue_due_date={"$(='>='&Date(floor(issue_due_date)-2))"}>} issue_due_date)

Re: Before a date

mparker123 — Tue, 14 Nov 2017 21:16:47 GMT

So that expression displays data but it is the same as the original straight table i snap shot in the beginning of this thread.

Re: Before a date

trdandamudi — Tue, 14 Nov 2017 21:20:43 GMT

I am sorry, I am not getting it. Can you paste a screen shot and explain please....

Re: Before a date

mparker123 — Tue, 14 Nov 2017 21:32:19 GMT

I figured it out i just put this "=If(issue_due_date>=today()-2,issue_due_date)" as a calculated demsions and left the other expression had i had it.

But now do you know how to text format a calculated dimension to show a date format of 'MM/DD/YYYY'

Re: Before a date

vishsaggi — Tue, 14 Nov 2017 21:38:11 GMT

Try

= If( issue_due_date>=today()-2, Date(Floor(issue_due_date), 'MM/DD/YYYY'))

Re: Before a date

Anonymous — Tue, 14 Nov 2017 22:07:51 GMT

A calculated dimension would work but may perform less optimally that this:

You would put this as your set analysis:

issue_due_date={'>=$(=today()-2)'}

Also I probably wouldn't count the date field. I would count project name or project id if there is one.

Re: Before a date

mparker123 — Wed, 15 Nov 2017 13:49:35 GMT

That didn't work in my set analysis it returned a blank display. I do like the idea of count(project name) instead of date. Is there anything else I could try.

Also, I am trying to set a format for the calculated dimension. My date field is a time stamp. I want to make it just 'MM/DD/YYYY'?

I tried the guys above and it returned some dates in the correct format but some in numbers.

Re: Before a date

vishsaggi — Wed, 15 Nov 2017 15:02:25 GMT

Try this?

= If( issue_due_date>=today()-2, Date(Num(Floor(issue_due_date)), 'MM/DD/YYYY'))

Re: Before a date

Anonymous — Wed, 15 Nov 2017 16:01:54 GMT

You'd be best off creating a date only field in your script. That's probably why you're having so much trouble.

Something like:

date(floor(issue_due_date),'M/D/YYYY') as [Issue Due Date]

And then use that field in your expressions and set analysis.

Re: Before a date

mparker123 — Wed, 15 Nov 2017 20:23:38 GMT

Thank you all for your help I found the solution to my problem. Below are the two expressions i used to solve my problem

Exprssion:

Count({<DEPT={"44840"}, epic_status-= {"Not Required", "Done"}, project_name-={"PMO IMS Pipeline","PMO Test"}>}epic_summary)

Calculated Deminsion:

=Date(If(epic_due_date>=today()-2,epic_due_date),'MM/DD/YYYY')