Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Evolutive chart with calculated months

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

1 Solution

Accepted Solutions
jebamalai
Valued Contributor

Re: Evolutive chart with calculated months

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

6 Replies
jebamalai
Valued Contributor

Re: Evolutive chart with calculated months

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

Not applicable

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.

Not applicable

Re: Evolutive chart with calculated months

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

Not applicable

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 Smiley Frustrated )

Best regards.

Not applicable

Re: Evolutive chart with calculated months

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

Not applicable

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.

Community Browser