topic Timestamp variable in Set Analysis in QlikView

Timestamp variable in Set Analysis

Wed, 16 Oct 2013 14:35:14 GMT

Hi all,

I know there is already a lot of questions on this subject, but I can't make it work 😞

I have a table with End Date under a Timestamp form, I've created a variable calendar object also with a Timestamp representation (RefDate), and I would like to show in my Set Analysis only the records whose end date is >= RefDate...

If I use the following the Now function in place of my RefDate, it works fine:

count({<EndDateTypePop = {">=$(=now())"}>} IndividualId)

NomNiv1	EndDateTypePop	Nb d'individus
Administrateur	31/12/9999 00:00	9
Collaborateur groupe		17
Détaché		6
Externe		72
Retraité		162
Salarié		781
Stagiaire	30/12/2013 23:00	1
Total		1048

But when I try to use my variable/calendar object, it does not work anymore..

I've tried

count(({<EndDateTypePop = {">=$($(RefDate))"}>} IndividualId)

count(({<EndDateTypePop = {">=$($(#RefDate))"}>} IndividualId)

count(({<EndDateTypePop = {">=$((=num(RefDate))"}>} IndividualId)

count(({<EndDateTypePop = {">=$((=timestamp(RefDate))"}>} IndividualId)

NomNiv1	End Date	Nb d'individus
Administrateur	27/03/2011 22:00	1
Administrateur	31/12/9999 00:00	9
Collaborateur groupe		21
Détaché		7
Externe		553
Retraité		162
Salarié		1248
Stagiaire		235
Vacancier		280
Total		2516

And I have either too much lines (as the red one supra), either a blank table

=> Any idea please????

Thank you,

Anne-Lise

Re: Timestamp variable in Set Analysis

swuehl — Wed, 16 Oct 2013 14:42:00 GMT

Try

=count( {<EndDateTypePop = {">='$(RefDate)'"} >} IndividualId)

so just $(RefDate) enclosed in single quotes.

Ah, another point: remove the seconds from your variable timestamp format, it is not matching your EndDateTypePop format.

Re: Timestamp variable in Set Analysis

Wed, 16 Oct 2013 14:46:16 GMT

In that case I have a blank table... but what do you mean by removing seconds from my variable? I hase the "Number" stand format Timestamp DD/MM/YYYY hh:mm:ss[.fff] , but if I remove seconds DD/MM/YYYY hh:mm I still have the bloank table 😞

Re: Timestamp variable in Set Analysis

swuehl — Wed, 16 Oct 2013 15:06:26 GMT

I also believe it's just a matter of formatting.

Could you upload a small sample application?

Re: Re: Timestamp variable in Set Analysis

Wed, 16 Oct 2013 15:11:30 GMT

Here is my file...

Re: Timestamp variable in Set Analysis

swuehl — Wed, 16 Oct 2013 15:17:04 GMT

Try

=count( {$ <EndDateTypePop = {">=$(=timestamp(RefDate))"} >} IndividualId)

Re: Timestamp variable in Set Analysis

Wed, 16 Oct 2013 15:20:54 GMT

It works 🙂 thank you very much!

Re: Timestamp variable in Set Analysis

swuehl — Wed, 16 Oct 2013 15:25:49 GMT

If you need to debug such things, clear the label of your expression in the chart, then QV will show the expression itself, with the dollar sign expansion performed. So you see the actual search string and might get an idea why it is not working.

Re: Timestamp variable in Set Analysis

kevinpintokpa — Thu, 02 Jul 2015 23:09:48 GMT

Nice, a good tip, thanks!