topic Re: p funtion with date range in QlikView

p funtion with date range

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

Hi All,

How can we use a date range expresson inside p() .

Currently i am using the below expression which is not working:-

MachineName=p({<Date={">=$(=date(floor(monthstart(addmonths(today()-4))),'DD-MM-YYYY'))"}*{'<$(=date(floor(monthstart(addmonths(today()-1))),'DD-MM-YYYY'))"} )

Re: p funtion with date range

jagan — Thu, 17 Mar 2016 03:56:20 GMT

HI,

Try like this

MachineName=p({<Date={">=$(=date(floor(monthstart(today(),-4)),'DD-MM-YYYY'))<$(=date(floor(monthstart(today(), -1)),'DD-MM-YYYY'))"} )

Hope this helps you.

Regards,

Jagan.

Re: p funtion with date range

HirisH_V7 — Thu, 17 Mar 2016 04:36:22 GMT

Hi,

May be like this,

=Only({<MachineName=P({< Date={">=$(=Date(Max(Date)-3))<=$(=Date(Max(Date)))"}>}MachineName)>}MachineName)

or

=Only({<MachineName=P({<Date={">=$(=Date(Today()-3))<=$(=Today())"}>MachineName)>}MachineName)

Hope this Helps,

Hirish

Re: p funtion with date range

Pulkit_Thukral — Thu, 17 Mar 2016 06:29:12 GMT

Hi Hirsh,

can the above expression be included within an expression ?

i tried it but it is giving an error.

Re: p funtion with date range

Pulkit_Thukral — Thu, 17 Mar 2016 06:31:23 GMT

Tried it but not working jagan.

Re: p funtion with date range

Kushal_Chawda — Thu, 17 Mar 2016 06:51:20 GMT

Make sure that your Date Field is in proper date format, try below

MachineName=p({<Date={">=$(=date(monthstart(addmonths(today(),-4))))<=$(=date(monthstart(addmonths(today(),-1))))"} )

Re: p funtion with date range

HirisH_V7 — Thu, 17 Mar 2016 07:01:08 GMT

Hi,

Your Data is Current or not?

if Current Go for Second expression, else go with First one,

Can you make a sample app with a sample data.That way we can see the issue!

-Hirish

Re: p funtion with date range

Pulkit_Thukral — Thu, 17 Mar 2016 08:04:47 GMT

it is in a proper date format still the expression is not working

Re: p funtion with date range

sunny_talwar — Thu, 17 Mar 2016 08:07:36 GMT

Can you share what is the format that you date field is in?

Re: p funtion with date range

Pulkit_Thukral — Thu, 17 Mar 2016 08:47:17 GMT

i checked with 2 formats-

date(Date,'MM/DD/YYYY')

and

date(Date,'MM/DD/YYYY hh:mm:ss')

Re: p funtion with date range

swuehl — Thu, 17 Mar 2016 08:54:51 GMT

'not working' is not really helping us to find out what is causing the issue.

Try

=Count(DISTINCT {< Date={">=$(=date(monthstart(addmonths(today(),-4))))<=$(=date(monthstart(addmonths(today(),-1))))"} >} Date)

or what ever set expression in your p() function you want to test in a text box.

It must return the distinct number of dates in your date range (if you are using a master calendar in your data model, you can easily compare to a calendar on your wall).

If this is 'not working', please specify what you expect to see and what you get.

Regards,

Stefan

Re: p funtion with date range

swuehl — Thu, 17 Mar 2016 08:56:47 GMT

pulkit thukral wrote:

i checked with 2 formats-

date(Date,'MM/DD/YYYY')

and

date(Date,'MM/DD/YYYY hh:mm:ss')

You need to check what the field format of the Date values is, e.g. by looking at a list box with default number formatting. If you are using Date() function, you are reformatting the field format.

Why don’t my dates work?

Dates in Set Analysis

Re: p funtion with date range

Pulkit_Thukral — Thu, 17 Mar 2016 09:15:40 GMT

Thanks for writing.

Please find my exact requirement mentioned below.

i Wrote an expression count of machines that were patched between nov-15 to feb-16.

This worked perfectly with the expression:-

count(distinct {<Patch_Status={'Success'},Dategenerated={">=$(=date(floor(monthstart(addmonths(today()-4))),'DD-MM-YYYY'))"}*{'<$(=date(floor(monthstart(addmonths(today()-1))),'DD-MM-YYYY'))"} >}machines

Now, i have to create a text box showing the count of machines that were patched bet nov-15 to feb 16 (as defined in top expression) but are not patched between aug-15 to oct-15 (i.e last 3 - 6 months).

Example:

Suppose 80 machines were patched between nov-15 to feb-16 as per the first expression.

Now , i need to find that among these 80 machines,how many machines were not patched between aug-15 to oct-15.

What could be the possible way of showing this count.?