6 Replies Latest reply: Jun 18, 2014 3:42 PM by Oswaldo Vasquez Branched to a new discussion. RSS

    Evolutive chart with calculated months

    Nancy Briceño Aguilar

      Hi,

       

      I have the following information in excel:

       

      Region Organization Account Month Actual Budget

      R1              O1         C1          Ene   100     110

      R1              O1         C1          Feb   110     100

      ...

      R1              O1         C1          Dic   100     110

      R1              O1        C2          Ene   100     110

      R1              O1         C2          Feb   110     100

      ...

      R1              O1         C2          Dic   100     110

       

      I need to make a Line chart with 2 lines:

      1) Montly evolution of actual expenses by account C1

      2) Montly evolution of budget expenses by account C1

       

       

      I have already made a straight table with this information:

       

      Trips.jpg

      I need to make one line chart for the "Domestic Trip Expenses" account and another for  "International Trip Expenses" account.

       

      Can you help me?

       

      Best Regards

      Nancy

        • Re: Evolutive chart with calculated months
          Jebamalai Dass

          Keep Month as your dimension and try

           

          =sum(if(Account='Domestic Trip Expenses,Actual) and replace the second type

           

          Try this and let me know

          • Re: Evolutive chart with calculated months

            Hi,

             

            try using set analysis, ex:

             

            Expression 1:

            sum({<Account={'C1'}>}Actual)

             

            Expression 2:

            sum({<Account={'C1'}>}Budget)

             

            And for get a chart similar of your image, try a pivot table wiht:

            Dimension: Region, Organization, Account and Month

            Expressions: Use above expressions.

             

            Finally pull Month Dimension over 2 expressions.

             

            Best regards.

            • Re: Evolutive chart with calculated months
              Nancy Briceño Aguilar

              Thanks guys!

               

              Now I have another little issue: Due to I have actual expenses from Jan to May and, despite I have budget from Jan to Dec, in the chart only shows the months and data from Jan to May.

               

              How I can force to show all the year?

               

              Thanks again

              Nancy

                • Re: Evolutive chart with calculated months

                  Hi,

                   

                  well... you have to create a master calendar with all months (Jan to Dec) and unselect "Supress or delete Zero Values" in your pivot table, this is on properties (of your chart) in Presentation Sheet.

                   

                  (Excuse my english :S )

                   

                  Best regards.

                    • Re: Evolutive chart with calculated months
                      Nancy Briceño Aguilar

                      Hi Oswaldo,

                       

                      I´m Ok with the part of unselect "Supress or delete Zero Values" in my chart in Presentation Sheet. But, you loose me with the master calendar thing ¿¿¿???

                       

                      Please, can you help me?

                       

                      Thanks

                        • Re: Evolutive chart with calculated months

                          Hi Nancy,

                           

                          Master calendar is a table dedicated to get calendar fields like Year, Month, Day, MonthYear ... and it's used to control time in a Qv aplication. I use 2 ways to create this table.

                           

                          From your fact table.

                          By autogenarate

                           

                          Example option 1:

                           

                          MasterCalendar:

                          Load

                               Date(OrderDate) as Date,

                               Year(OrderDate) as Year,

                               Month(OrderDate) as Month,

                               Day(OrderDate) as Day,

                               MonthName(OrderDate) as MonthYear,

                               Week(OrderDate) as Week

                          Resident FactTable;

                           

                          Example option 2:

                           

                          Let vDateMin = Num('01/012013');
                          Let vDateMax = Num('12/01/2014');

                           

                          TempCalendar: 
                          LOAD
                          $(vDateMin) + RowNo() - 1 AS DateNumber, 
                          Date($(vDateMin) + RowNo() - 1) AS TempDate 
                          AUTOGENERATE 1 
                          WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

                           

                          CALENDAR: 
                          LOAD
                          Date(TempDate)   AS [FECHA REPORTE], 
                          Day(TempDate)   AS Dia,
                          Month(TempDate)  AS Mes,
                          Year(TempDate)   AS Anio, 
                          Week(TempDate)   AS Semana,
                          WeekDay(TempDate)  AS CalendarDayName, 
                          DATE(monthstart(TempDate), 'MMM-YYYY') AS CalendarMonthYear,
                          MonthName(TempDate) AS MonthYear,
                          'Q' & Ceil(Month(TempDate)/3) AS Quarter, 
                          'S' & Ceil(Month(TempDate)/6) AS Semester
                          RESIDENT TempCalendar
                          ORDER BY TempDate ASC;

                           

                          DROP Table TempCalendar;

                           

                          The second option is better for your request because creare all of dates between min date and max date and doesn´t happen if there are not data for some months (in your case Jun to Dec).

                           

                          Best Regards.