Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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.
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
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.