topic Count({<Date={'<=$currentDate)'}>}Date) doesn't return correct number?! in QlikView

Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 11:01:27 GMT

Hallo!

I'm running in some issue while using the count-function.

Like header shows i want to count dates, wich are smaller(before) the currentDate.

CurrentDate is a variable and changes.

But the function always returns the wrong(for me, maybe function is working correct) number...

If i have this table:

Serial	Date	Price
123	01-01-2013	5
234	02-02-2013	6
345	02-02-2013	7
123	04-05-2013	8

and use function:

count({<Date={'<=$(currentDate)'}>}Date)

i would expect following result:

currentDate	count({<Date={'$(currentDate)'}>}Date)
01-01-2013	1
...	1
02-02-2013	3
...	3
03-03-2013	3
...	3
04-05-2013	4

But its not:(

If i check using FieldValue('Date',count({<Date={'<=$(currentDate)'}>}Date)) it returns wrong date.

It's much more in future, means the count-function returns a too big value...

Example:

currentDate = 23-01-2013

count({<Date={'<=$(currentDate)'}>}Date)=3

FieldValue('Date',count({<Date={'<=$(currentDate)'}>}Date))=03-03-2013 (not always 2 days later, i didnt find an order)

Seems like i do not understand how the function works^^

What is wrong?

Thanks for reading,

Sarah

'Count({<Date={'$currentDate)'}>}Date)' adjusted to 'Count({<Date={'<=$currentDate)'}>}Date)', after friendly hint from Joerg Hildebrandt

Re: Count({}Date) doesn't return correct number?!

omyahamburg — Mon, 01 Jul 2013 11:22:11 GMT

If you change to count({<Date={'<=$(currentDate)'}>}Date)

this gives you the # of dates up to the date in the variable.

Works for me.

best regards

Joerg

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 11:38:39 GMT

Yea you're right, but i also tried <= 😕

Anyway thank u!

And no, it dont returns the correct value...

I will cahnge the original post^^

I use this function so many times before, but this time its not working...

I realy doont know whats wrong:-(

Re: Count({}Date) doesn't return correct number?!

omyahamburg — Mon, 01 Jul 2013 11:48:13 GMT

Can you post sample data ?

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 12:12:46 GMT

No,sry i cant:(

It's to complicated to rebuild it wit different data and names...

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 12:19:27 GMT

scrabmle your data under document properties and reduce data - Can you share then?

Otherwise, make ur data a numeric. And then count the difference between today and the date, then you will get the count of days

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 12:36:45 GMT

What is that scramble good for?

Does it make the data anonymous?

Another fact that i found is, that the higher the result is, the higher is the failure.

Means if i expected 8 its 10,if i expect 18 its 24....

And its not the numeric difference between "Today" and start, because sometime more than one serial_no occurres per day.

And somedays nothing occurres...

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 12:39:26 GMT

scramble makes your data into nonsense. This means we cannot interpret anything from your dataset because its been manipulated.

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 12:50:49 GMT

Hm, i cant see any difference but for more decimals?

And this project is much too big and includes data wich is much to sensitive...

I fear my company wouldn't agree...

I always check my data while creating some table boxes.

If i count the days "per hand" i see it returns the wrong result...

Is there any possiblity that a table box doesn't shows whole data(no supress zero ect. is deactivated)?

If u know some technology's to proof i can do anything^^

It seems like my understanding of count-function is well, maybe some issue's in data?

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 13:12:22 GMT

Ok, strange to say but a missing distinct was the problem...

If i use count({<Date={'<=$(currentDate)'}>}distinct Date) it is like expected...

But how makes this sense?

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 13:38:27 GMT

maybe you got the same date twice and it add them. Therefore you get too many.. I dont know, its hard to say without dataset

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 14:00:43 GMT

In the end the only explanation can be that i have duplicated rows in my table...

Wich are not shown in my table box and i dont know why^^

Does tableBox supresses duplicated rows?

Has QV the ability to save equal rows in one table?

Re: Count({}Date) doesn't return correct number?!

IAMDV — Mon, 01 Jul 2013 15:44:19 GMT

Hi Sarah,

Use the List Box with frequency property checked to see if you have duplicate values. Table Box would show duplicates if you add unique key or all field in to Table Box. But be careful with Key Fields …they would make it inconsistent.

Cheers,

www.QlikShare.com

Re: Count({}Date) doesn't return correct number?!

Mon, 01 Jul 2013 16:09:00 GMT

Oh you are so great:)

A lot of thanks to all of u!

I have duplicated dates, but thats ok because different serial_no can occurre a the same date.

Thats why distinct confuses me here...i want all dates,normally^^

Only problem i see is when whole rows in my table are equal(means every value).

Is this possible corresponding to QV?

QV doesn't allows me to copy tables by loading them like : load * resident table;

I'm so confused