topic Re: Re: help needed - App not summing up in QlikView

help needed - App not summing up

Mon, 29 Sep 2014 13:56:09 GMT

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.

Re: Sum all where date is same

geert_gelade — Mon, 29 Sep 2014 14:11:14 GMT

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).

Re: help needed - App not summing up

Mon, 29 Sep 2014 14:15:08 GMT

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.

Re: Sum all where date is same

martinpohl — Mon, 29 Sep 2014 14:15:17 GMT

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

Re: help needed - App not summing up

m_woolf — Mon, 29 Sep 2014 14:16:36 GMT

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

Re: help needed - App not summing up

JonnyPoole — Mon, 29 Sep 2014 14:22:12 GMT

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$);

Re: Re: help needed - App not summing up

Mon, 29 Sep 2014 14:29:41 GMT

Thanks to all.

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

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

Thanks.

Re: Re: help needed - App not summing up

martinpohl — Mon, 29 Sep 2014 14:31:01 GMT

look at the help

Re: Re: help needed - App not summing up

JonnyPoole — Mon, 29 Sep 2014 14:35:52 GMT

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.