topic Re: Dimension alter my table pivot in QlikView

Dimension alter my table pivot

jose_vargas — Thu, 27 Dec 2018 15:59:58 GMT

I have some problems with pivoting table. I have made my table with 7 dimensions (Plaza, Via, TipoTransit, Detalle, Año, Mes, dia) and 3 expressions (Cant,% PArcial,% Error) For the expression% Error, I make a division between Cant. And the sum total of Cant for Type Transit = Transit Tag. This table is fine.

For Dimension, this is the code:

Cant

Count(Contador)

%Parciales

Count(Contador)/Count(TOTAL <Año_Est,Mes_Est,Día_Est,Agr_Estacion,[Cod. Vía]> Contador)

% Error

if(GrupoVariable = 'Tránsito Tag',Count(Contador)/Count(TOTAL <Año_Est,Mes_Est,Día_Est,Agr_Estacion,[Cod. Vía],GrupoVariable> Contador))

Now what I need is just a Table with fewer fields, I need only 4 dimensions (Palza, Via, Año, Mes) and 2 expressions (Cant,% Error), but I need the same values of the first graphic. When I remove the dimensions, the "% Error" field does not show the correct values.

If I dont remove dimension "Tipo Transito" and "detalle", the values show well...

The code for Cant is:

Count(if(GrupoVariable = 'Tránsito Tag' and Respuesta = 'Tag Manual',Contador))

% Error

if(Respuesta = 'Tag Manual',Count(Contador)/Count(TOTAL <Año_Est,Mes_Est,Día_Est,Agr_Estacion,[Cod. Vía],GrupoVariable> Contador))

When I remove the 2 dimensions "Tipo" and "Detalle, this happen

I hope you can help me.
Thank you.

Re: Dimension alter my table pivot

sunny_talwar — Thu, 27 Dec 2018 16:13:53 GMT

You might need to use Aggr() function with all or most of your first chart dimensions.

Re: Dimension alter my table pivot

jose_vargas — Thu, 27 Dec 2018 17:33:43 GMT

But how can I apply AGGR when we use several dimensions, for example in TOTAL I do it for several dimensions

Count(TOTAL <Año_Est,Mes_Est,Día_Est,Agr_Estacion,[Cod. Vía],GrupoVariable>

How can I do for AGGR, I've looked for examples, but it only works with one dimension. It would be something like this

if(Respuesta = 'Tag Manual',Count(Contador)/Aggr(count(Contador),<Año_Est,Mes_Est,[Día_Est],[Agr_Estacion],[Cod. Vía],[GrupoVariable]>))

Not work 😞 Do you have nay example?.

Thanks

Re: Dimension alter my table pivot

sunny_talwar — Thu, 27 Dec 2018 17:40:40 GMT

I was thinking more like this

Aggr(If(Respuesta = 'Tag Manual', Count(Contador)/Count(TOTAL <Año_Est,Mes_Est,[Día_Est],[Agr_Estacion],[Cod. Vía],[GrupoVariable]> Contador)), Your7DimensionsHere)

I don't have an example, but if you are able to share a sample and let me know the required output from the sample shared, I can work on getting an expression which can work for you.

Best,
Sunny

Re: Dimension alter my table pivot

jose_vargas — Thu, 27 Dec 2018 19:19:55 GMT

I Share my file.

https://drive.google.com/open?id=1Z9bJcsEeIl6y8LQGpB79fkV32kYbhKia

I need to do something like that, but without these two columns

Re: Dimension alter my table pivot

sunny_talwar — Thu, 27 Dec 2018 19:23:05 GMT

Unfortunately, I am unable to open google drive. Would you be able to directly post your file here?

Re: Dimension alter my table pivot

jose_vargas — Thu, 27 Dec 2018 19:35:50 GMT

ok there I send it

Re: Dimension alter my table pivot

sunny_talwar — Thu, 27 Dec 2018 19:44:02 GMT

Try this

Aggr(if(Respuesta = 'Tag Manual',Count(Contador)/Count(TOTAL <Año_Est,Mes_Est,Día_Est,Agr_Estacion,[Cod. Vía],GrupoVariable> Contador)), [Año_Est], [Mes_Est], [Agr_Estacion], [Cod. Vía], GrupoVariable, Respuesta)

Re: Dimension alter my table pivot

jose_vargas — Thu, 27 Dec 2018 20:08:20 GMT

Thanks a lot... Its works (Y)