topic Re: Group the same dates together in a bar chart in QlikView

Group the same dates together in a bar chart

Thu, 30 Oct 2014 09:34:06 GMT

Hi,

I am completely new to QV and need help please!

I have a Product ID and the date it was created. I want to show how many product IDs were created on the same day using a bar chart. However when I create it it puts each date separately instead of grouping the same dates together and summing the product IDs. ID's.

I want to group the same dates together and sum the product IDs which were created on the same date.

Thank you in advance

Message was edited by: Rebecca Harris

Re: Group the same dates together in a bar chart

Thu, 30 Oct 2014 10:14:14 GMT

Check if your dates are having timestamp or not.

or in the script use

date(DateField,'YYYY-MM-DD')

--Singh

Re: Group the same dates together in a bar chart

anbu1984 — Thu, 30 Oct 2014 10:21:30 GMT

Can you post sample data

Re: Group the same dates together in a bar chart

Thu, 30 Oct 2014 14:55:14 GMT

Hi Both,

Thank you for your response.

Here is my current script

Hi,

Sorry I am completely new to this. Where do I put it in my script?

This is what my script looks like at the moment-

Directory;

LOAD Project,

Projectid,

Created,

FROM

[..\Documents\source.xlsx]

(ooxml, embedded labels, table is general_report);

Below is what I am trying to do- as you can see the same dates appear in separate bars

THANK YOU,

Re: Group the same dates together in a bar chart

rubenmarin — Thu, 30 Oct 2014 15:03:40 GMT

Hi Rebecca, like Agnad said, it will be:

Directory;

LOAD Project,

Projectid,

Date(Created, 'DD/MM/YYYY') as Created,

FROM

[..\Documents\source.xlsx]

(ooxml, embedded labels, table is general_report);

Re: Group the same dates together in a bar chart

Anonymous — Thu, 30 Oct 2014 15:03:53 GMT

Rebecca

In your Bar Chart object what is the expression you are using? As it looks like that always evaluates to 1.

Bill

Re: Group the same dates together in a bar chart

its_anandrjs — Thu, 30 Oct 2014 15:10:22 GMT

It seems you use any count expression try the expression with Distinct key word in expression, or please provide which expression you use.

Sum(DISTINCT MetricName)

Count(DISTINCT MetricName)

Regards

Anand

Re: Group the same dates together in a bar chart

Thu, 30 Oct 2014 15:11:18 GMT

Hi All,

I have put it into my script and although it has formatted the dates better it still hasn't grouped the same dates together.

For example on the 17/07/14 there are two Project IDs created however it has put it as two separate columns.

The expression I have used is count(projectid)

Thank you for your help

Rebecca

Re: Group the same dates together in a bar chart

rubenmarin — Thu, 30 Oct 2014 15:13:41 GMT

So, maybe:

Directory;

LOAD Project,

Projectid,

Date(Floor(Date(Created)), 'DD/MM/YYYY') as Created,

FROM

[..\Documents\source.xlsx]

(ooxml, embedded labels, table is general_report);

Re: Group the same dates together in a bar chart

Anonymous — Thu, 30 Oct 2014 15:13:55 GMT

Try this in your script :

Directory;

LOAD Project,

Projectid,

date ( floor ( date(Created) as Created,

FROM

[..\Documents\source.xlsx]

(ooxml, embedded labels, table is general_report);

Re: Group the same dates together in a bar chart

Thu, 30 Oct 2014 15:17:40 GMT

Thank you Ruben & Bill!!

Ruben- works perfectly!!

Thank you so much!

Rebecca

Re: Group the same dates together in a bar chart

Anonymous — Thu, 30 Oct 2014 15:25:51 GMT

Rebecca

QlikView [and most databases] store DateTimes as a number with :

The number before the decimal point representing the date
The decimals after the decimal point as the fraction of the date

When displayed as a date it ignores the decimals for display purposes but underneath still stores the decimals. You can see this if you display the field as a timestamp.

What the floor() function does is knock off the decimals so you are left with a pure date.

If want just the time you can use the frac() function that knocks off everthing before the decimal point :

time(frac(YourDateTimeField))

Bill

Re: Group the same dates together in a bar chart

Thu, 30 Oct 2014 15:29:27 GMT

Hi Bill,

Thank you so much for taking the time to explain these things to me.

So if I wanted to knock off the days and just group together the month/ year would I just do the following-

Date(Floor(Date(Created)), 'MM/YYYY') as Created,

Thanks again,

Rebecca

Re: Group the same dates together in a bar chart

Anonymous — Thu, 30 Oct 2014 15:43:19 GMT

Rebecca

Nope, you'd need to use the MonthStart() function.

Month ( monthstart ( Date(Created) ) ) as [Created Month] ,

In the QlikView Desktop Help in the "Date and Time Functions" section you'll find loads of date & time functions.

Bill