Group the same dates together in a bar chart

[#1]

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

[#2]

Check if your dates are having timestamp or not.

or in the script use

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

--Singh

[anbu1984]

Can you post sample data

[#4]

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,

[rubenmarin]

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

[Anonymous]

Rebecca

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

Bill

[its_anandrjs]

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)

Or

Count(DISTINCT MetricName)

Regards

Anand

[#8]

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

[rubenmarin · Accepted solution]

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

[Anonymous]

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