14 Replies Latest reply: Jan 20, 2010 11:32 AM by Michael Steedle RSS

    Last 12 months

    beciafreestyle

      Hallo everybody,

       

      I'm trying to show on the graph last 12 months (only) and till now I tried to work with calculating dimension. As it didn't work I thought to add new position (last12Months) in script. Could anybody help? In attachment my Calendar;-)

       

      Thnak you in advance,

      Beata Jablonska

        • Last 12 months

          Hi,

          i suggest you to use "set analysis" in expression. I attached qv application with this expression in it.

          I hope this will help you :)

            • Last 12 months
              beciafreestyle

              Dear Milda,

               

              Could you send it as an expression?

               

              I'm grateful,

              Beata

                • Last 12 months
                  beciafreestyle

                  My point is, that I could't use set analysis (at least in the way I know it). I'm calculating expression by Sum (invoiceAmount) and aggregate it 12 steps back (so I can show Moving Annual Total). When I want to show recent 12 months, the graph is useless, as it starts to calculate expression from the first shown month (instead of keeping old aggregation) I tried few things, and nothing helps, so I though script could be an idea.

                  Above the graph I need:

                  • Last 12 months

                     

                    sum(
                    {$<
                    CalendarDate={'*'},
                    CalendarDay={'*'},
                    CalendarMonthAndYear={'*'},
                    CalendarQuarter={'*'},
                    CalendarWeek={'*'},
                    CalendarWeekAndYear={'*'},
                    CurYTDFlag={'*'},
                    WeekDay={'*'},
                    CalendarYear={'*'},
                    CalendarMonth={'*'},
                    CalendarDate={$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}
                    >}
                    1)

                    Here i used sum() function as an example. You can use any other function instead.



                      • Last 12 months
                        beciafreestyle

                        Dear Milda,

                        Firstly thank you for your effort. Secondly, unfortunately it doesn't worl:( I have error:

                        Error in expression:
                        { is not a valid function

                        MasterCalendar:
                        LOAD
                        CalendarDate={(internal error)}>}1,

                        I put expression as following:

                        MasterCalendar:
                        LOAD
                        TempDate AS CalendarDate,
                        Day(TempDate) AS CalendarDay,
                        Week(TempDate) AS CalendarWeek,
                        Weekday(TempDate) AS WeekDay,
                        Month(TempDate) AS CalendarMonth,
                        Year(TempDate) AS CalendarYear,
                        'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
                        Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
                        Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
                        Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
                        CalendarDate={$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}>}1)

                        I hope you can help me,

                        Greetings,

                        Beata

                  • Last 12 months

                    So you need that, for example, at column "1-1-2010" would be the sum of "1-1-2009.....1-1-2010" periods?

                    • Last 12 months
                      Michael Steedle

                      One solution is generating a rolling months table to associate to your calendar table. Please see the attached.

                      One note: I changed how you were generating CalendarMonthAndYear so it could be sorted numerically.

                        • Last 12 months
                          beciafreestyle

                          Dear Michael,

                           

                          Thank very much for this file! The only thing is, that now maximum date is December 2010 (insted of current- January), so it's not exactly what I wanted. I am trying to find why maximum date is December 2010, and when it will be changed it will be perfect! If you see the mistake, please let me know.

                          Acha, and I would like to ask you for one more thing- could you next time copy/paste changes you made, insted of attaching a file? Unfortunately I'm still Personal Edition user:(

                           

                          Thank you in advance,

                          Best greetings,

                          Beata

                            • Last 12 months
                              Michael Steedle

                              It was built assuming that the calendar ran through today instead of the end of the current year. To make it work based on today, add the following clause to the AllMonths load (row 38 in the script):

                              WHERE CalendarMonthAndYear <= $(vDateToday)

                              Also, I didn't make it clear before, but all you would need to do to build the chart is use Rolling Month as the dimension and sum(InvoiceAmount) as the expression.

                                • Last 12 months
                                  beciafreestyle

                                  It's amazing Michael! Moving annual total works perfectly! I changed AllMonths load and it works fine but still MasterCalendar shows date till December 2010. As a reasult all my max expressions have to be changed. I tried to make it alone it script, but my abilities are still too low. Could you help one more time?

                                  Thank you very much,

                                  Beata

                                   

                                   

                                   

                                    • Last 12 months
                                      Michael Steedle

                                      I used the Master Calendar table from the original QVW you attached, which also goes through the end of 2010. Look at your LOAD...WHILE statement for TempCalendar, and you should be able to see why it generates rows through the end of the year.

                              • Last 12 months

                                Good morning Beata,

                                now you should be able to open Michaels file.

                                Best wishes

                                Rainer