Date range in Set Analysis

[rjn]

Hi

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

[tamilarasu]

Hi,

Check below expression

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

[Gysbert_Wassenaar]

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.

[rjn]

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

[tamilarasu]

Could you post vMinDate and vMaxDate values.?

[Gysbert_Wassenaar]

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?

[rjn]

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)

[tamilarasu]

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)

[Gysbert_Wassenaar · Accepted solution]

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.

[rjn]

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