topic Re: Date comparison with set analysis and variable in QlikView

Date comparison with set analysis and variable

Mon, 19 Oct 2015 22:13:07 GMT

Hi all,

sorry for the question, I read a lot in the forums but still could not solve my problem:

I try to count the number of records in a table where the field KC_Instanz.BESTELLT_AM falls into a timeframe between variable eAuswerteSTART and eAuswerteENDE.

The variables are defined in script

SET eAuswerteSTART=MakeWeekDate(vAuswertung_Year,vAuswertung_KW,3)+MakeTime(12);
SET eAuswerteENDE =MakeWeekDate(vAuswertung_Year,vAuswertung_KW+1,3)+MakeTime(11,59);

and the variables vAuswertung_Year,vAuswertung_KW can be set using an InputBox.

When checking them with a textbox:

&'Auswertung für: KW '&vAuswertung_KW&chr(10)
&'Von: '&Timestamp($(eAuswerteSTART)) &chr(10)
&'Bis: '&Timestamp($(eAuswerteENDE)) &chr(10)

all looks good, I get a date-time-format as "12.03.2015 12:002 for the start...

Now trying to count the records I try...

= count (DISTINCT {1<KC_Instanz.BESTELLT_AM = {">$(=eAuswerteSTART) <= $(=eAuswerteENDE)"}>} KompC.DATEINAME)&chr(10)

... but it always returns to 0.

Anyone can help?

Thanks a lot Oliver

Re: Date comparison with set analysis and variable

Gysbert_Wassenaar — Wed, 21 Oct 2015 13:58:13 GMT

Try count ({1<KC_Instanz.BESTELLT_AM = {">$(eAuswerteSTART)<= $(eAuswerteENDE)"}>} DISTINCT KompC.DATEINAME)

Re: Date comparison with set analysis and variable

swuehl — Wed, 21 Oct 2015 14:02:31 GMT

Take care that your expanded variables match the format of your timestamp field.

So maybe try

= count (DISTINCT {1<KC_Instanz.BESTELLT_AM = {">$(=Timestamp($(eAuswerteSTART)))<= $(=Timestamp(eAuswerteENDE)))"}>} KompC.DATEINAME)

edit:

Re: Date comparison with set analysis and variable

Fri, 23 Oct 2015 09:26:52 GMT

Hi there,

thanks for your answers. Unfortunately they still return 0 into my text box...

I tried these expressions in text box to find if formats match and when selecting one record it looks good:

=Timestamp($(eAuswerteENDE))&chr(10) -->Do 22.10.2015 12:00
=Timestamp(KC_Instanz.BESTELLT_AM )&chr(10) -->Di 20.10.2015 16:22
=KC_Instanz.BESTELLT_AM &chr(10) -->Di 20.10.2015 16:22

=if(KC_Instanz.BESTELLT_AM >=(Timestamp($(eAuswerteSTART))),'found') -->found
=if(KC_Instanz.BESTELLT_AM >=$(eAuswerteSTART),'found') -->found

So data formats seem to match, data can be found but still the set analysis does only print 0.

I cannot figure out what I do wrong…

An example table contains these data

KC_Instanz.BESTELLT_AM	ISCO.STATUS	KompC.DATEINAME
Do 22.10.2015 11:27	final	TEST1__MDE01__001_010_F20_025.004_000_017.pdx
Di 20.10.2015 16:22	final	TEST2__DDXE701__025.007_022_153.pdx
Di 20.10.2015 12:23	final	TEST3__MXEVD172G__025.000_000_099.pdx

=count (distinct {$<ISCO.STATUS={'final'}>} EE_Komponente.NAME) -->3 (so it works)

But when I start using timestamps together with set analysis I always get 0:

Even these (more basic) expresseions returns 0 each

& count ( {$<KC_Instanz.BESTELLT_AM= {"<(=now())"} >} KompC.DATEINAME)&chr(10)

& count ( {$<KC_Instanz.BESTELLT_AM= {"<(=(timestamp(=now()))"} >} KompC.DATEINAME)&chr(10)

Any idea someone? Something wrong with my data?

Thanks Oliver

Re: Date comparison with set analysis and variable

swuehl — Fri, 23 Oct 2015 10:09:50 GMT

In your last two expressions, you are missing dollar signs for dollar sign expansion:

& count ( {$<KC_Instanz.BESTELLT_AM= {"<$(=now())"} >} KompC.DATEINAME)&chr(10)

& count ( {$<KC_Instanz.BESTELLT_AM= {"<$(=timestamp(now()))"} >} KompC.DATEINAME)&chr(10)

Your standard time format seems to be modified to include the weekday abbreviation (Mo, Di, Mi). is this correct?

Doing something like

=if(KC_Instanz.BESTELLT_AM >=(Timestamp($(eAuswerteSTART))),'found') -->found

will not necessarily compare the text formats, when using dual() values, these comparisons will match on number representation, I believe. So it's not a 100% indicator that your formats for set analysis match.

If you want to avoid the trouble with formats, you can either create a numeric field and match with numbers only (but looking at the fact you are using floating points (Timestamps), you might run into trouble even then (separator not matching).

Or force QV to match the numeric interpretation with your existing field, like in your if() statement:

count ( {$<KC_Instanz.BESTELLT_AM= {"=KC_Instanz.BESTELLT_AM < $(=num(now(),'#.######','.',','))"} >} KompC.DATEINAME)

edit:

If you can upload a small sample QVW, that would help us to help you.

Re: Date comparison with set analysis and variable

Fri, 23 Oct 2015 12:23:27 GMT

Hey thanks a lot,

that gets me a lot closer to the solution - seems to be a formatting issue indeed. You are right, I had modified the standard timestamp format. Now using num() looks better:

Only I still I got an issue:

&count({$<KC_Instanz.BESTELLT_AM = {"=KC_Instanz.BESTELLT_AM > $(=num($(eAuswerteSTART),'#.######','.',','))"} >} KompC.DATEINAME) &chr(10)
&count({$<KC_Instanz.BESTELLT_AM = {"=KC_Instanz.BESTELLT_AM <= $(=num($(eAuswerteENDE),'#.######','.',','))"} >} KompC.DATEINAME) &chr(10)

work out fine counting the correct numbers

Only when I try to combine both I still get 0:

&count({$<KC_Instanz.BESTELLT_AM = {"=KC_Instanz.BESTELLT_AM <$(=num($(eAuswerteENDE),'#.######','.',',')) >$(=num($(eAuswerteSTART),'#.######','.',',')) "} >} KompC.DATEINAME) &chr(10)

Unforthunately I must not upload my qvw due to company rules... I may only try to create similar data-qvw on the weekend if it helps.

Thanks Oliver

Re: Date comparison with set analysis and variable

swuehl — Fri, 23 Oct 2015 14:56:12 GMT

With the modified search expression logic you need to write

count({$<KC_Instanz.BESTELLT_AM = {"=KC_Instanz.BESTELLT_AM <$(=num($(eAuswerteENDE),'#.######','.',',')) AND KC_Instanz.BESTELLT_AM >$(=num($(eAuswerteSTART),'#.######','.',',')) "} >} KompC.DATEINAME)

Re: Date comparison with set analysis and variable

Fri, 23 Oct 2015 15:05:39 GMT

Hi Swuehl,

thanks so much!!!!! You made my weekend 🙂 !!!!

Finally with your modification now it works!

Best regards Oliver