• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Chart Axis

I am having problems with the x axis on a bar chart

If the user picks Last Full Month - I want to show the data by day i.e. 01/05/2013, 02/05/2013. This works correctly

If the user picks Last Full Day I want to display the data  by hour i.e. 1:00,2:00 - at the moment this does not display in the correct order i.e. displays 1:00,10:00,11:00,12:00 instead of 1:00, 2:00, 3:00 etc.

My formula is as below

if {?datechoice} = 'Last Full Day' then totext(hour({Command.sentdatetime}),2,"" ) else
if {?datechoice} in [ 'Last Full Month'] then totext(cdate({Command.sentdatetime}),'dd/MM/yy' )

Is there a way I can correct this to show it in the right order?

Using CR2008
0
halifaxman
Asked:
halifaxman
  • 3
  • 2
  • 2
1 Solution
 
mlmccCommented:
Try

ToText ({Command.sentdatetime}),'yy/MM/dd' )


mlmcc
0
 
halifaxmanAuthor Commented:
Thanks for the answer

The data for day works correctly i.e. totext(cdate({Command.sentdatetime}),'dd/MM/yy' ) - this displays in the correct order 01/05,02/05 etc

The data for hour totext(hour({Command.sentdatetime}),2,"" ) - does not display in the correct order, this displays  1:00,10:00,11:00,12:00  etc. I need it to display as 1:00,2:00,3:00 etc
0
 
mlmccCommented:
Try

totext(hour({Command.sentdatetime}),"00:00" )

mlmcc
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
halifaxmanAuthor Commented:
Tried the above, got the error

Bad number format string
0
 
James0628Commented:
If you just want the hour, without the minutes, but want to include ":00" for the minutes (as in your examples), you can use:

totext({Command.sentdatetime},"hh:00")

 Or, to get just the hour:

totext({Command.sentdatetime},"hh")


 Note that I didn't use the Hour function.  ToText is extracting the hour from the datetime and formatting it.

 Interestingly, ToText doesn't like a ":" in a format string for a number (like the result of the Hour function).  That's why you got an error on mlmcc's suggestion.  But it allows it when formatting a datetime, including "hh:00" (which gives you the hour, followed by ":00").

 James
0
 
mlmccCommented:
Try

totext(hour({Command.sentdatetime}),"00" ) + ":00"

mlmcc
0
 
James0628Commented:
Yeah, that would work too.  Never thought of that.  :-)

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now