Combined Graphics with Mixed Dimension
10/19/2011 3 comentarios
I have occasionally found customers who want to replicate with QlikView’s graphs that are being made with other tools such as Excel or even PowerPoint or some other flexible tool to “draw” graphics and charts very particulars.
Of course these tools do not have the power of data analysis (Data Discoverer) that has QlikView but QlikView seems to impose strong restrictions on the types of graphics we can do and what we can.
For example: Can we show in the same graph the cumulative production of the year, the expected (budget) and a monthly production for a year? The answer is YES (using some tricks and tips) and here is the example:
This is a combined type graph (General tab of the properties of the graph) that blends bar and lines. So far nothing particular. If we observe the dimension axis (X axis) on the one hand we see that we have dates to the right and “other things” on the left.
In particular, the client wanted to see Budgeted production of fuel oil for the YTD (Year To Date Budget – beginning of the year to date) as a bar, the cumulative production for the same period as another bar, and monthly production as a line.
Here in this example as we can achieve this dimension “combined” with QlikView:
1 – The secret is in the Data:
Suppose we have a table (excel, access, sql or another) from which we import the data in the following format:
Perdiodo (Period) Toneladas (Tons) 05/2011 1420 06/2011 1135 ... .. ... ...
The period column certainly we’ll use to assemble the right side of the x axis.
To assemble the left side of the axis we must “inject” in the dimension values that do not correspond to actual date and are smaller than the smallest possible date (to appear on the left in a ordered sequence) and associate their amounts accumulated in tons, calculated and add YTD (actual and budgeted) for each year. For the case we will assume that month -1 will be used to “YTD Budget” and month 0 (zero) will be used for “Cumulative YTD”
Periodo (Period) Toneladas(Tons) -1/2011 15 979 ---> corresponds to "YTD Budget" 00/2011 9321 ---> corresponds to "Cumulative YTD" ... ... ... ... .. ... ...
Of course, this script should do so at the load (or better in a intermediate stage respecting the architectural transformation of the solution) amounting to (eg) for each year.
2 – Prepare the graph. Create the dimension:
Once “cooked” the data we create a new chart type “Combined” and add a dimension calculated using the following expression:
= If (left (Period, 2) = '00 ', dual ("Acumulado YTD", Period), if (left (Period, 2) = '-1', dual ('BUDGET. YTD',Period), Period ) )
That is, if the month is “00” (double zero) returns the value 00 internally and displayed as “Cumulative YTD” (dual function), if the month is “-1” dual returns a -1, “Presup.YTD” pair, otherwise (months 1 to 12) returns the same period.
Due to the default sort ascending numerical values of the dimension will be in the following order:
-12011, 002011, 012011, 022011, 032011 ... ... .. 122011
and thanks to the dual we’ll see:
Presup.YTD; Cumulative YTD, 012,011, 022,011 ... ... ... 122,011
3 – Create the expressions:
In particular this chart uses 3 expressions
And the expressions to synchronize the data and the dimmension’s values are:
YTD: = sum (if (left (Period, 2) = '00 'Tons)) //just applies is period= "00" REAL: = sum (if (month (period)> 0, Real)) //line in the graph PRESUPUESTO : = sum (if (left (Period, 2) = '-1' Tonnage)) //just applies is period= -1 then shows YTD Budget
Finally we indicate that accumulated YTD Budget YTD and Real are bars and smooth line is used to do the finishing touches and voila: you get your mixed chart with mixed dimension !
I hope you found this usefull !