Combined Graphics with Mixed Dimension

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 !

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

3 Responses to Combined Graphics with Mixed Dimension

  1. Hola Pablo,

    me ha gustado mucho este ejemplo pero cuidado que lleva algun error en las expressiones y en la dimension. Seria una buena cosa si pudieras agregar un archivo con el QVW aunqué he conseguido replicar todo sin muchos problemas.

    Queria preguntarte como al valor en cada punto de los datos le acercarle el Tn?
    No encuentro la manera de conseguir.

    Saludos
    Luca Jonathan Panetta

  2. pparnisari dice:

    Luca:

    Vale tu comentario ya que la técnica es algo “artesanal” y hay que buscar un modo de estandarizarla (mediante un excel de configuración o similar como dices tu).
    Para mostrar “Tn” el los puntos en primer lugar debes activar “Values on data points” en las propiedades de la expresión y luego en la solapa “Number” en la máscara de formato (“Format Pattern”) escribes (por ejemplo) “Tn ###########.###”, o cualquier otro símbolo que quieres aparezca.

    Slds.
    Pablo Parnisari

  3. Hola Pablo,

    me descrubriste un mundo, nunca he pensado que bastaba eso😀
    Gracias.

    Otra pequeña pregunta, en post no veo en los datos el campo Real, pero en las expressiones lo usas. Yo en la replicación de tu ejemplo utilizé Tons.

    Me ha gustado mucho este post, sigues así.

    Saludos
    Luca

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: