topic Comparing dates in set analysis in QlikView

Comparing dates in set analysis

arixooo123 — Mon, 23 Feb 2015 06:07:36 GMT

my Expression looks like this :

avg({<PurchaseDate<=2015-01-15>}Sales

as you can see I have a Date column (PurchaseDate) and a simple date 2015-01-15

in sql i would simply :

PurchaseDate <= convert(datetime,'2015-01-15',suitable Style)

thanks in advance

its_anandrjs — Mon, 23 Feb 2015 06:28:56 GMT

Try with

Avg({<PurchaseDate = {'<=2015-01-15'} >} Sales )

You can use it dynamically also

Regards

Anand

manojkulkarni — Mon, 23 Feb 2015 06:29:38 GMT

set vCompareDate=Date('2015-01-15','YYYY-MM-DD')

avg({<PurchaseDate={"<=$(vCompareDate)"}>}Sales

Make sure both the dates are in same format

Anonymous — Mon, 23 Feb 2015 06:31:19 GMT

Your set analysis syntax is incorrect. Please try below expression

avg({<PurchaseDate = {'<=2015-01-15'}>}Sales)

Regards,

Greeshma

Mon, 23 Feb 2015 06:40:39 GMT

Hi,

if you want to compare a field value with a specific date or variable then use code like this

set vCompareDate=Date('2015-01-15','YYYY-MM-DD')

avg({<PurchaseDate={"<=$(vCompareDate)"}>}Sale)

----------------------------------

if you are facing problem then covert the date into number format then use (num(PurchaseDate)).

set vCompareDate=num(Date('2015-01-15','YYYY-MM-DD'))

avg({<PurchaseDate={"<=$(vCompareDate)"}>}Sales)

Regards

Vimlesh

arixooo123 — Mon, 23 Feb 2015 08:01:34 GMT

the above expression does not work, i don't know why.

but, what if i try this?

avg({<Year(PurchaseDate)<2015,Month(PurchaseDate)<1>}Sales)

it is like in sql i try:

where

datepart(year,PurchaseDate)<2015 and DatePart(Month,PurchaseDate)<1