topic Re: Using Dates in Set Analysis in QlikView

Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 09:09:35 GMT

I'm trying to do something that I think would be fairly simple, but am failing horribly.

I want to use something like >=2010-01-31 in my date field. I have tried this with formulas and variables, using single and double quotes and equals signs all over the place inside and outside of brackets preceded by $ signs etc. Nothing seems to work.

I have a single dimension called Term, that determines how far back I look (with respect to the current date) in terms of data to include in my sum.

I start with vCurrentDate = Max(Total ReturnDate). I then create vStartDate = MonthEnd($(vCurrentDate),-(Term-1)).

If I then use the following expression: Count({$<ReturnDate={"$(='>='&$(vDate))"}>} ReturnDate), I get the same value for all Terms (where Term = 1,3,6,12,36,60 say).

Where am I going wrong? Is it my date format i.e. YYYY-MM-DD that is creating problems in Set Analysis?

Any help would be appreciated.

Thanks.

Joao.

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 09:11:27 GMT

Here's an image of the result:

Term	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))	Count({$<ReturnDate={">="}>} Return)
		74
1	2013-03-31	74
3	2013-01-31	74
6	2012-10-31	74
12	2012-04-30	74
36	2010-04-30	74
60	2008-04-30	74

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 10:05:22 GMT

Try this:

Count({$<ReturnDate={'>=$(vDate)'}>} ReturnDate)

This is a formula that works in my applications:

sum({$<TransDate={'<=$(BSDurationMEnd)'}>} NLValue)

Make sure the date is a date though. If you're not sure try creating a second variable that converts it using Date($(vDate)).

Jonathan

Re: Using Dates in Set Analysis

shreyashetty_ge — Wed, 12 Jun 2013 10:19:08 GMT

Hi,

Firstly check the date formats whether it is same throughout i.e the variable in which date is stored and the one which you are comparing, in any text object.

Then try this below set analysis .

Count({<ReturnDate={">=$(vDate)"}>} ReturnDate)

Hope it works.

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 11:41:27 GMT

Firstly, apologies for jumping from vStartDate to vDate. They are in fact the same and this is a typo.

Your suggestions don't work. I think the vStartDate is a date because it formats as one if I leave the formatting as expression default. If I change the formatting to Integer, it shows as a number (as per table below). The first column is just Term. The second is vStartDate (shows nothing). The third and fourth are $(vStartDate) formatted differently (third is default, and fourth is integer). The last column is your suggested formula i.e. Count({$<ReturnDate={'>=$(vDate)'}>} ReturnDate)

It has now changed from 72 to 0 (I don't know if that helps).

Thanks again.

Term	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))	MonthEnd(Max(TOTAL ReturnDate),-(Term-1))

1	2013-03-31	41365
3	2013-01-31	41306
6	2012-10-31	41214
12	2012-04-30	41030
36	2010-04-30	40299
60	2008-04-30	39569

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 11:47:05 GMT

Sorry, also does not work. Could I be defining my variables wrongly i.e. do I need '=' in them. Perhaps my definitions above were badly stated, but it does seem to produce the correct dates in theother columns. It is somehow just not doing the set analysis with these dates correctly.

Any other suggestions?

Thanks.

Re: Using Dates in Set Analysis

jagan — Wed, 12 Jun 2013 11:50:18 GMT

HI,

Try like this

=Count({<ReturnDate={">=$(=vDate)"}>} ReturnDate)

If you need distinct date then use
=Count({<ReturnDate={">=$(=vDate)"}>} DISTINCT ReturnDate)

Before that check the date formats in date dimension and variable are same.

Hope this helps you.

Regards,

jagan.

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 12:58:58 GMT

Sorry, but still doesn't work.

How do I check date formats in dimension and variable? They are numbers representing dates i.e. get formatted as dates when displayed but internally stored as numbers (see above).

Will it help if i leave out the day i.e. just compare to YYYY-MM?

Thanks.

Joao

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 13:25:53 GMT

How about wrapping a date() function around the vDate ?

Count({$<ReturnDate={'>=Date($(vDate))'}>} ReturnDate)

Jonathan

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 13:31:02 GMT

I have tried that with no effect.

I can't understand why this is so difficult when it should be so easy. I must be doing something wrong.

Both my ReturnDate and my variables are definitely dates (numbers), not text! Is this the problem?

Thanks.

Joao.

Re: Using Dates in Set Analysis

Anonymous — Wed, 12 Jun 2013 14:00:45 GMT

I've also had problems in the past, especially with balance sheet budgeting calculations.

Try taking the > sign away to see if a straight equals test return anything.

Count({$<ReturnDate={'=Date($(vDate))'}>} ReturnDate)

Jonathan

Re: Using Dates in Set Analysis

shreyashetty_ge — Thu, 13 Jun 2013 06:16:05 GMT

You already have come close to the solution, its your date format which is creating problm. The format of the Vdate and ReturnDate should be same.Also, if you are not using any '=' while creating the variable then you use '=' while using it in set analysis.

Re: Using Dates in Set Analysis

Anonymous — Thu, 13 Jun 2013 06:39:24 GMT

So how do I make the format the same, or how is it creating problems? Please can you suggest a solution?

Also, your third sentence doesn't appear to be correct. If I change the definition of vDate from MonthEnd(Max(TOTAL ReturnDate),-(Term-1)) to =MonthEnd(Max(TOTAL ReturnDate),-(Term-1)), it stops working altogether i.e. even $(vStartDate) doesn't produce a result.

Thanks.

Joao.

Re: Using Dates in Set Analysis

Anonymous — Thu, 13 Jun 2013 06:53:37 GMT

I've added an example file.

Thanks.

Joao.