Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Dimension alter my table pivot

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.

qlik1.jpg

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...

qlik2.jpg

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

qlik3.jpg

 

I hope you can help me.
Thank you.

Labels (1)
1 Solution

Accepted Solutions
Highlighted

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)

image.png

View solution in original post

8 Replies
Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
Creator
Creator

I Share my file.

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

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

qlik4.jpg

Highlighted

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

ok there I send it

Highlighted

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)

image.png

View solution in original post

Highlighted
Creator
Creator

Thanks a lot... Its works (Y)