8 Replies Latest reply: Jan 23, 2012 5:39 AM by Anil Chakelam RSS

    How to show Month Totals in chart

      Hi,

       

          Here i have requirment like i have to show the month wise tickets inline chart,, how to break the year wise tickets into month wise...

           In DB i have date column there i have 2011 tickets created in a oraganization.

          what i did here is i have return calender  script to showmonth names inX-axis and in expression i took Total count of ticketsbut its notgiving correct result

           one example here is forJan/2011 chartshould show 34000 tickets created but in chart it isshowing only 9tickets,, and in 2011 year total 440000+ tickets  created this count isshowing excatly end of the chart. so how to show month wise tickets in chart  So requesting your help on this.........

       

                                                             I have uploaded a sample file also please check......

        • How to show Month Totals in chart
          Stefan Wühl

          Your ticket table is not linked to your master calendar, so you will get a total for each month.

           

          You could try using

          =Count (if(CalendarDate=daystart(T_STMP_PROB_RSLVD), I_TCKT))

           

          to get a count of tickets for each month. But it would be much better to link the tables, like

           

          Directory;

          LOAD I_TCKT,

               C_TCKT_STAT,

               T_STMP_PROB_RSLVD,

            daystart(T_STMP_PROB_RSLVD) as CalendarDate

          FROM

          TIC.xlsx

          (ooxml, embedded labels, table is Sheet1);

            • How to show Month Totals in chart

              Hi swuehl thnx for replay i forgot to rename this column T_STMP_PROB_RSLVD in sample file but in my actual file both tables are linked....

               

                                               I tried with your expression in expresion tab but its giving error could you please eleborate you answer

                • How to show Month Totals in chart
                  Stefan Wühl

                  I believe it is not enough to rename the field T_STMP_PROB_RSLVD, it is a timestamp, so you won't get a match with a CalendarDate, unless for the timestamps that are equal to daystart.

                   

                  Have you tried using

                  daystart(T_STMP_PROB_RSLVD) as CalendarDate

                   

                  ?

                    • How to show Month Totals in chart

                      swuehl i tried it and  its working perfectly thnx a lot and  we can add multiple expressions also in same chart am i right .....................

                        • How to show Month Totals in chart
                          Stefan Wühl

                          Sure you can add multiple expressions - or have I misunderstood your request?

                            • How to show Month Totals in chart

                              Your understanrding is perfect...could you please explain about Daystart Function

                                • How to show Month Totals in chart
                                  Stefan Wühl

                                  Each date or timestamp has a numerical representation, this allows to calculate with dates or timestamps (e.g. calculating the difference of two dates).

                                   

                                  For example, today() is 2012-01-02, with a numerical representation of 40910. But now() is a timestamp around 1:26 am of today's date and its numerical representation is 40910.059872685.

                                  The decimal places are specifying the time of day. daystart() ist returning the whole integer, which is equal to 12 am and this value is also used for the date itself.

                                   

                                  So if I create a calendar with dates, I get only integer values like 40910 (and the text representation). If I link my timestamp field to my calendar, I won't get a match for any timestamps that are different from 12 am. So I need to convert my timestamps to dates before linking them to my calendar.

                                   

                                  And if you are still asking about the daystart function, this is what the Help says:

                                   

                                  DayStart(timestamp [, shift = 0 [, dayoffset = 0]])

                                  Returns a value corresponding to a timestamp with the first millisecond of the day contained in timestamp. The default output format will be the TimestampFormat set in the script. Shift is an integer, where the value 0 indicates the day which contains date. Negative values in shift indicate preceding days and positive values indicate succeeding days. If you want to work with days not starting midnight, indicate an offset in fraction of a day in dayoffset, e.g 0.125 to denote 3am.

                                  Examples:

                                  daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'

                                  daystart ( '2006-01-25 16:45', -1 ) returns '2006-01-24 00:00:00' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'

                                  daystart ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25 12:00:00' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000'

                                   

                                   

                                  Hope this helps,

                                  Stefan

                                    • Re: How to show Month Totals in chart

                                      Hi Swehl can you please help me on this post......

                                       

                                       

                                      Hi All,

                                       

                                             I have a intersting requirement i.e. i have to display the data in Hierarchy view(Tree Structure) like

                                       

                                                      A

                                                 B         C

                                              D   E    F  G

                                       

                                       

                                      Here i  have attached my sample file and xl file also. I have requirement

                                       

                                      1)If you select Austrila from list box in Hierarchy structure we dnt havedata for City listboxes for both side then those listboxes shoulddisappear (wheneevr we dnt have the data for any list box that shoulddisapper) and suppose we dnt have data for Region either left or rightside from hierarchy that list box should disappear and the arrow  has directly point to State list box.

                                       

                                      2)Based on a Column am dividing the whole structure into 2 parts(left,Right) but in my report am not able to get that correctly am gettingsame data for both sides for this am using a condition like this""=If((Type ='Region') & (LOC ='Left'),Trim(Desc))"".

                                       

                                      So please help me to get out of this