Date Hierarchy shows day of month not day of year

[jwieczerzak]

When I try to drill down usig a date hierarchy, I get a list of days by day number.  What I really want is to increase the granularity of the data.

Here is the use case:  I set up a line chart using Date as an axis.  Then I add a Count of something as a value.

When I first see the chart is shows all of the counts within the year, Click down on hierarchy anmd it shows all of the counts within the Quarter, Month, etc.

But when I click to Days, it shows me a list fo days (1..35) and it aggregates the data for all of the days with that day number (1, 2, ..31).

What I would expect to see is a drill down into granularity.  So if I have three months worth of data, I would see all 90 days worth of data not just 31.  1/1, 1/2...3/30, 3/31

What am I doing wrong?

[Eric_Zhang]

---

@jwieczerzak wrote:

When I try to drill down usig a date hierarchy, I get a list of days by day number.  What I really want is to increase the granularity of the data.

Here is the use case:  I set up a line chart using Date as an axis.  Then I add a Count of something as a value.

When I first see the chart is shows all of the counts within the year, Click down on hierarchy anmd it shows all of the counts within the Quarter, Month, etc.

But when I click to Days, it shows me a list fo days (1..35) and it aggregates the data for all of the days with that day number (1, 2, ..31).

What I would expect to see is a drill down into granularity.  So if I have three months worth of data, I would see all 90 days worth of data not just 31.  1/1, 1/2...3/30, 3/31

What am I doing wrong?

---

That is the hierarchy drilling down by design. What do you mean "all 90 days worth of data".

[Anonymous]

Hi Eric,

Old thread, but it's asking the question I need answered.

I also have a bar chart plotting figures over days time, and I'm using a slicer to change the date range.

When I use the slicer to select one month of data only, the chart will show each day of the month (1st, 2nd, 3rd, 4th, etc.) just like it should - good.

I then change the slicer to show 3 months worth of data, BUT, the chart is still showing only "each day of the month" and not "each and every single day for that 3 month period".

i.e. It's still showing each of the 30/31 "days of the month", but now it's summed the 1st of each month and made that a single data bar, same with the 2nd, 3rd, 4th, etc., so that there are still only 30 data points on the chart even though I am slicing to 90 days.

What I want to see is all 90 days with their own data bar, not just 30 data bars with each day-of-the-month summed up into a single data bar.

[Eric_Zhang]

---

@Anonymous wrote:

Hi Eric,

Old thread, but it's asking the question I need answered.

I also have a bar chart plotting figures over days time, and I'm using a slicer to change the date range.

When I use the slicer to select one month of data only, the chart will show each day of the month (1st, 2nd, 3rd, 4th, etc.) just like it should - good.

I then change the slicer to show 3 months worth of data, BUT, the chart is still showing only "each day of the month" and not "each and every single day for that 3 month period".

i.e. It's still showing each of the 30/31 "days of the month", but now it's summed the 1st of each month and made that a single data bar, same with the 2nd, 3rd, 4th, etc., so that there are still only 30 data points on the chart even though I am slicing to 90 days.

What I want to see is all 90 days with their own data bar, not just 30 data bars with each day-of-the-month summed up into a single data bar.

---

@Anonymous

What do you put on the X axis? It shall work if you put a date instead of date hierarchy.

[ForrestC]

Eric,

If you use the date instead of the date hierarchy, it does display all 90 days in Stephen's example.  But then you lose the ability to do any hierarchy groupings.  

Is there a way to change this?  I want to be able to drill down from Year to Quarter to Month (to Week?) to Day, without grouping in a confusing way. 

Example:  I have sales data from 2017 and 2018.  When I drill down from year to quarter, I want to see 2017 and 2018 turn into: 2017Q1, 2017Q2, 2017Q3, 2017Q4, 2018Q1, 2018Q2, 2018Q3, 2018Q4 instead of just 4 columns for Q1 to Q4.  It doesn't make much business sense to sum up 2017Q1 and 2018Q1 into one column called Q1, so I'm surprised this is the default behavior.  Further, I'm a little frustrated that I can't change this default...or can't figure out how to change it.

I've worked around this by creating custom columns called "YYYY0-QQ" or "YYYY-MM" but then I lose the nice drill down hierarchy.  Am I missing something?

[Anonymous]

I am having this same issue and it seems like such a simple thing to be able to do. I think I may have solved for this. On my axis I have my date field with the hierarchy. I only have Month and Day as the pieces of the hierarchy there. On my chart, I use the expand next level and it shows all dates. When using the show next level, I had the same issue. I am not sure how this expand next level works though as it seemed buggy and wasn't working the first time (it just gave me a blank chart).

[SuraMan]

@Anonymous, @Anonymous , @ForrestC 

How about creating the date hierarchy with the columns as below?

Year (eg data: 2017, 2018, 2019 etc)

Month (eg data: Jan 2017, Feb 2017, etc)

Date (eg data: 01-January-2017)

The trick is that the lower members in the hierarchy have to contain the "paths" so that two months of different years are not bundled together.

Then Years can be drilled down to relevant Months within that year and Months can be drilled down to relevant Dates of the month and year.