Static min & max for dimension on chart

[#1]

Hello all,

I have the following dimension in my load script:

TIME(Timestamp, 'hh') & ':00-' & TIME(Timestamp + (1/24), 'hh') & ':00' AS HOUR

which produces 08:00-09:00 (for sales between 08:00 and 08:59), 09:00-10:00 (for sales between 09:00 and 09:59) etc.

I would like to fix the dimension axis on my chart to display 08:00-09:00 to 18:00-19:00 (opening times only) as there are only minor sales outside of this period that I do not want displayed on the chart.

Currently my chart looks like this:

I would like the graph to display just the boxed area.

I've tried adjusting the Static min and Static max boxes on the Axes tab in the chart properties but as of yet can't get it to work.

Any ideas would be greatly appreciated as always.

[matt_crowther]

You could try adding a calculated dimension to control the times displayed in the axis.

Something like: =if(Hour>=08:00 and Hour<=19:00,Hour)

You may need to fiddle with the syntax as the dimension won't be recognised as a number but that should be easy enough.

Cheers

[#3]

Hi APS

So you want the line to start with the last 0 hour before you have values but ignore the succeeding 0 values?

Can you set the starting hour to a very small value (0.01) and suppress zero values in the presentation setting?

Juerg

[#4]

Juerg Maier JmiD GmbH Schweiz wrote:
Hi APS
So you want the line to start with the last 0 hour before you have values but ignore the succeeding 0 values?
Can you set the starting hour to a very small value (0.01) and suppress zero values in the presentation setting?
Juerg<div></div>

Hi, I want to fix the dimension axis so that it displays sales between the hours of 08:00-19:00.

Although I have created the HOUR field (see original post), I also have the Timestamp field in my document which is in the following date format 25/10/2009 15:10:25.

I've tried fixing the min value with

=DATE(RIGHT(TIMESTAMP, 8), 'hh:mm:ss') = '08:00:00'

but it didn't work. The chart simply displays: No numeric values on x-axix

[#5]

Hi

If you know which hours you want limit the data in the load?

Juerg

[#6]

Juerg Maier JmiD GmbH Schweiz wrote:
Hi
If you know which hours you want limit the data in the load?
Juerg<div></div>

Hi Juerg,

I need to bring in all the data in the load but for display purposes limit the dimension axis on the graph. Otherwise I would do as you suggest and limit the load.

Thanks for the responses.

APS

[#7]

Create a second table that loads only the data the graph has to show?

[johnw]

Your field is a TEXT field. It needs to be a NUMERIC field to use a static min and max. An easy way to do that is to use the dual() function:

dual(time(Timestamp,'hh')&':00-'&time(Timestamp+1/24,'hh')&':00'),floor(frac(Timestamp)*24)) as HOUR

Frac(Timstamp) eliminates the days. Multiplying by 24 gives you the hour. The fractional part is then the minutes and seconds. Floor removes the minutes and seconds, leaving you only the hour. Set your static min to 7, and your static max to 18 (not 19, because you actually only want through 18:59:59.9999 if I understand, and that will be floored to 18).

Matt Crowther's solution should work as well, with the possible drawback that calculated dimensions can sometimes be slow.

[#9]

John Witherspoon wrote:
Your field is a TEXT field. It needs to be a NUMERIC field to use a static min and max. An easy way to do that is to use the dual() function:
dual(time(Timestamp,'hh')&':00-'&time(Timestamp+1/24,'hh')&':00'),floor(frac(Timestamp)*24)) as HOUR
Frac(Timstamp) eliminates the days. Multiplying by 24 gives you the hour. The fractional part is then the minutes and seconds. Floor removes the minutes and seconds, leaving you only the hour. Set your static min to 7, and your static max to 18 (not 19, because you actually only want through 18:59:59.9999 if I understand, and that will be floored to 18).
Matt Crowther's solution should work as well, with the possible drawback that calculated dimensions can sometimes be slow.

John, thanks for the response.

I've created a new numeric field based on TIMESTAMP (as you suggested) but it results in some of the values falling into the wrong category. For example, 06:00-07:00 now includes values between 06:00-07:15. Also, the dimension axis now displays just a number e.g. 10 rather than 10:00-11:00.

I'll continue to investigate a solution using both your suggestion and also Matt Crowther's (although I can't get that to work either).

Thanks,

APS

[johnw · Accepted solution]

Looks like a combination of the two approaches may be best, and for performance, you might want to load the calculated dimension as a real dimension. See attached.