Evolutive chart with calculated months

[#1]

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:

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

[Anonymous · Accepted solution]

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

[#3]

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.

[#4]

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

[#5]

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.

[#6]

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

[#7]

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.