topic Re: Date range in Set Analysis in QlikView

Date range in Set Analysis

rjn — Thu, 17 Mar 2016 07:15:31 GMT

I am trying to achieve the below expression in Set Analysis.

=Only( If(OrderDate >= $(vMinDate) AND OrderDate <= $(vMaxDate), OrderID) )

The OrderDate field is a Date format ('YYYY-MM-DD').

Please help.

Regards,

J. Ravichandra Kumar

Re: Date range in Set Analysis

tamilarasu — Thu, 17 Mar 2016 07:20:04 GMT

Hi,

Check below expression

=Only({<OrderDate = {">=$(vMinDate)<=$(vMaxDate)"}>} OrderID)

Re: Date range in Set Analysis

Gysbert_Wassenaar — Thu, 17 Mar 2016 07:21:56 GMT

If OrderDate is really a date field and not a text field then something like this should work:

=Only({<OrderDate={'>=$(vMinDate)<=$(vMaxDate)'}>} OrderID)

If it doesn't then you need to check if OrderDate really contains dates. Use the Date# function in the script as necessary to make dates from text strings.

Re: Date range in Set Analysis

rjn — Thu, 17 Mar 2016 07:31:36 GMT

Hi Gysbert,

The OrderDate is a Date field and the results are not showing up when used

Only({<OrderDate={'>=$(vMinDate)<=$(vMaxDate)'}>} OrderID) and

Only({<OrderDate={">=$(vMinDate)<=$(vMaxDate)"}>} OrderID)

vMinDate and vMaxDate are variables and has values, but unable to view any records in the Stright Table

Regards,

J. Ravichandra Kumar

Re: Date range in Set Analysis

tamilarasu — Thu, 17 Mar 2016 07:37:02 GMT

Could you post vMinDate and vMaxDate values.?

Re: Date range in Set Analysis

Gysbert_Wassenaar — Thu, 17 Mar 2016 07:38:45 GMT

What are the expressions in the variables? Perhaps you should change them so the return numbers only or dates in the format of the OrderDate field. Can you post a small qlikview document that demonstrates the issue?

Re: Date range in Set Analysis

rjn — Thu, 17 Mar 2016 07:39:56 GMT

vMinDate = '01/02/2014'
vMaxDate = '01/13/2016'

OrderDate field contains dates between the above range and format is 'MM/DD/YYYY' (used Date# function)

Re: Date range in Set Analysis

tamilarasu — Thu, 17 Mar 2016 07:40:28 GMT

You can specify the vMinDate and vMaxDate format in set expression.

=Only({<OrderDate = {">=Date(Date#($(vMinDate),'YourFormat'),'YYYY-MM-DD')<=Date(Date#($(vMaxDate),'YourFormat'),'YYYY-MM-DD')"}>} OrderID)

Re: Date range in Set Analysis

Gysbert_Wassenaar — Thu, 17 Mar 2016 08:00:50 GMT

So the variables contains strings and not dates. You can use the date# function in the set analysis expression like Tamil Nagaraj shows above. Or you can do it in the variables.

And the orderdate field contains dates with the default document date format. You used MM/DD/YYYY to turn the text string into a date, but it will not necessarily get that date format. If in the script the DateFormat variable is set with for example SET DateFormat='DD-MM-YYYY'; then your date# function will create dates with the DD-MM-YYYY format.

Re: Date range in Set Analysis

rjn — Fri, 18 Mar 2016 10:25:54 GMT

Hi Gysbert,

Hi Tamil Nagaraj,

Thanks for your support, the issue is fixed. There was a null in the OrderDate field due to which the Set Analysis was not working, but its fixed and working fine.

Regards,

J. Ravichandra Kumar