Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help needed - App not summing up

Hi All,

Any one help me to explain why below behaviour?

Why my Date list box showing 2 records for same day?

If load via Inline it is summing all records where date is same, but from XLS not summing up why?

Thanks.

1 Solution

Accepted Solutions
martinpohl
Partner - Master
Partner - Master

Look at the field Date, change the format to timestamp.

This date has two timestamp. although it is the same Date, Qlikview handle it as two dataset.

load

date(floor(Date)) as date

from your data

Regards

View solution in original post

8 Replies
geert_gelade
Creator
Creator

Strange, I think it has something to do with the formatting of the dates in your xls-file. When I update the date-column in xls (just retyping the dates), the result in qlikview is ok (so same dates are combined).

Not applicable
Author

Hi,

Thanks for reply.

If I am loading data from database from 2 tables where as both have Date fields, How we can load as same Date format from 2 different tables (make sure same date format)?

Thanks.

martinpohl
Partner - Master
Partner - Master

Look at the field Date, change the format to timestamp.

This date has two timestamp. although it is the same Date, Qlikview handle it as two dataset.

load

date(floor(Date)) as date

from your data

Regards

m_woolf
Master II
Master II

In your Excel workbook, Rows 6 and 7 have dates. Rows 8-12 have timestamps.

JonnyPoole
Former Employee
Former Employee

Your excel has some datetimes in it . Just strip the times out so that each day is recognized as the same value instead of multiple values (with multiple times). Use floor() as follows:

tab1:

  LOAD   Date(floor(Date)) as Date,

     Id,

     Id1Count,

     ID2,

     Id2Count

FROM

[sample.xls]

(biff, embedded labels, table is Sheet1$);

Not applicable
Author

Thanks to all.

I have to use Date(floor(Date)) as Date.

Any document which will explain all these kind of Key words?

Thanks.

martinpohl
Partner - Master
Partner - Master

look at the help

JonnyPoole
Former Employee
Former Employee

Good question.  The help files are extensive with many samples but a shortened list i think is what you are after. Probably a list of common date handling techniques .

Here are a few. You'll find more searching for 'dates' and filtering on 'documents'.

http://community.qlik.com/docs/DOC-2811

http://community.qlik.com/docs/DOC-3102

http://community.qlik.com/docs/DOC-4247

http://community.qlik.com/docs/DOC-1208

Couple things for this example:

1. If qlik recognizes the source values as dates or timestamps (dates with exact times), it will assign a numeric value to them. A whole number denotes a day. The decimal portion denotes a fraction of a day or a time.

2. Your list box is showing the source values as dates, but since there are multiple times on the date, you get a few values.

3. This is very common. Quick solution is to round the date number down effectively stripping out any record of the time . You can use floor() to do this.  Date() just makes sure it will be formatted as a date in the UI.