23 Replies Latest reply: Apr 19, 2011 3:59 AM by At titude RSS

    Help required in timeline pivot table!

      Hi

      I want to pass Week/Year/FinancialYear/etc... instead of Date in the attached pivot table. I tried to create a group by adding Week(Date), Year(Date),etc in that group and passed the created group instead of Date but it didn't work.

      Can anyone helpme out on this please.

        • Help required in timeline pivot table!
          Logesh Jayaraman

          Hi ,

          Can you please post some sample data how it should look like.

          • Help required in timeline pivot table!
            Neetu Singh

            Hi,

             

            At scripting level, please specify same date format in all dates as -

             

             

            DATE (

             

            (StartDate+IterNo()-1),'DD/MM/YYYY') AS date



            use this format in all rest date field and then derive week(date) as week, year(date) as year.

             

            It will work.

             

            Regards

            Neetu Singh

             

            • Help required in timeline pivot table!

              It's not working, because of your expression. When you switch to weeks, years, etc. anything that includes more that one dates, will result in multiple records within one set of dimension values and therefore you need an aggregation function in order to accommodate these multiple records so that you end up with 1 value. In this case a simple SUM around your expression will do it. Check the attached file.

                • Help required in timeline pivot table!

                  Hi Iassen

                  Your trick has worked yet again! Many thanks to you...

                   

                    • Help required in timeline pivot table!

                      Glad to hear it! Enjoy! :)

                      Thanx for the points! :)

                        • Help required in timeline pivot table!

                          Hi Iassen

                          In the sample application which you have provided the solution above. If I add MonthName(date) in the group chart are displayed as shown in the figure. I don't want that to display the something like "##'. I think that is because of less to display the data. Could you please tell me how that could be resolved. I am sorry if it is very simple. But please do let me know how to solve that issue. Please do it only when you are free.

                          Please make sure that it should not display "##" any more for what ever the time line pass in the group.

                           

                           

                           

                           

                            • Help required in timeline pivot table!

                              It's displaying it because it doesn't have enough room to show the full month name. Try increasing the width of the column or decreasing the font size.

                                • Help required in timeline pivot table!

                                   


                                  Iassen Ivanov wrote:
                                  It's displaying it because it doesn't have enough room to show the full month name. Try increasing the width of the column or decreasing the font size. <div></div>


                                  Yes I agree with your point. I cannot the increase the width anymore it should be same only. May I know where I can reduce the font size please.

                                    • Help required in timeline pivot table!

                                      Hi Iassen

                                      I am not too sure what the below code does in the expression. Only thing I am able to understand is that it is couting the number of (date) as the selection in group(Week/Year/Weekname/MonthName). If I am right is it required to show display it there. As I just want to show the color there. I don't want to show the sum(date) there. Infact it is not visible also as both the text and BG color are same.

                                      Could you please tell me any other alternative for this please.

                                       



                                      Sum(IF(StartDate<=date AND EndDate>=date,1,0))



                                       

                                    • Help required in timeline pivot table!

                                      Hi Iassen

                                      As per your suggestion I tried to reduce the font size (Go To: Properties --> Font --> Reduced the font size) but it is looking very very small. They may ask me to increase the size of the font but they will never ask to reduce it. So is there is any way to overcome this problem please.

                                      One more thing which I couldn't understand is that

                                      Year is displaying 3 digits and Week is displaying 2 digits but it is not showing up "##" because of no room in space. Whereas monthname with only 2 digits is having short of room.

                                      Please don't feel bad that I am disturbing you. I am just sharing you the outcome.

                                       

                              • Help required in timeline pivot table!

                                Hi Iassen

                                The same application which was working with the sample data(may be smaller date range) seems to be working but it is not working with the real time data(May be with bigger date range). Could you please help me out. It is very very urgent as I need to show the demo. Please help me out!

                                  • Help required in timeline pivot table!

                                    Hi All

                                    The application which seems to be working as per Iassen suggestion suddenly doesn't seems to be working(May be because of longer date range). In the application which is attached above I have commented out "date" from the dimension and also have commented out expression and have put 1 as i am getting 'out of object memory' error.

                                    Please add "date" in the dimension and remove 1 and uncomment the expression to work with. Hope some one can provide me some solution at the earliest.

                                     

                                     

                                  • Help required in timeline pivot table!

                                     


                                    Iassen Ivanov wrote:
                                    It's not working, because of your expression. When you switch to weeks, years, etc. anything that includes more that one dates, will result in multiple records within one set of dimension values and therefore you need an aggregation function in order to accommodate these multiple records so that you end up with 1 value. In this case a simple SUM around your expression will do it. Check the attached file. <div></div>


                                    With the help sample application that you have given after trying that got to know that table box contains 1224 rows of data but what is getting in the chart(Report) is only 1208. Also once I add date in the Time(Group) the application is getting crashed. Could you please have a look at it when you are free. It is little urgent for me.

                                    Please look into the attached application and provide some solution. First pririoty is to get the rows correct then we can add date in the group.

                                    Hope to receive positive reply from you.

                                      • Help required in timeline pivot table!

                                        Hi Iassen

                                        After doing further investigation about it. Got to know the reason for missing rows is just because in those rows StartDate > EndDate :-). But still it would be great if something can be done with the performance of the chart(With and without date field in the group). The performance of the chart is still slow with and without "date" being added with the group.

                                        Please give some solution when you find time.

                                         

                                         

                                         

                                          • Help required in timeline pivot table!

                                            Hi All

                                            Following are problem which is there in the solution provided by Rakesh(Please refer the sample application attached my rakesh above):

                                            1. Following rows with same date are ignored: (23668 - A1)

                                            23668 - 31/03/2011 15:07:00 - 31/03/2011 15:07:00

                                            2. Following rows are colored in the chart in end date but there is no color in the start date:(47640 and 47645 - A1)

                                            47640 - 05/04/2011 11:21:00 - 06/04/2011 11:21:00

                                            47645 - 10/04/2011 11:36:33 - 11/04/2011 11:36:33

                                            3. 16 rows are missing from the chart as the startdate > enddate. This is not the mistake in the chart. It is something the mistake with the data. Just sharing this for your information. Only above points is the concern.

                                            4. Infact there by other reason for missing rows. That I couldn't find it out!

                                            Still I am trying to resolve this issue from long time but it get solved yet. Hope some one can provide the solution for this soon.