topic Re: Evolutive chart with calculated months in QlikView

Evolutive chart with calculated months

Wed, 18 Jun 2014 15:51:47 GMT

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

Re: Evolutive chart with calculated months

Anonymous — Wed, 18 Jun 2014 15:55:06 GMT

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

Wed, 18 Jun 2014 16:10:20 GMT

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

Wed, 18 Jun 2014 16:47:32 GMT

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

Wed, 18 Jun 2014 17:00:30 GMT

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

Wed, 18 Jun 2014 18:58:07 GMT

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

Wed, 18 Jun 2014 19:42:45 GMT

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.