topic Re: Pivot table with calculated dimension and expression depending of contract level in QlikView

Pivot table with calculated dimension and expression depending of contract level

Sun, 13 Nov 2016 18:58:13 GMT

Hi friends

I'm working in a QV project where i want to show customer follow-ups.

I have this data:

Example:

LOAD * INLINE [

Cliente, Fecha, Hora, Actuacion

Juan, 01/02/2016, 15:00, Pide presupuesto

Jose, 01/02/2016, 15:05, Pide presupuesto

María, 01/02/2016, 16:00, Pide presupuesto

Juan, 02/02/2016, 18:20, Le envío oferta

Jose, 02/02/2016, 19:05, Le envío oferta

María, 02/02/2016, 20:00, Le envío oferta

Juan, 03/02/2016, 15:00, Llama para solicitar rebaja

Jose, 04/02/2016, 15:05, Llama para consultar detalles

Juan, 06/06/2016, 18:00, Realiza pedido

];

And I have a pivot table with Cliente, Fecha, Hora as dimensions plus one calculated dimension as "aggr(Max(Date(Fecha)), Cliente)" where I get the last date when i've contacted each customer.

In expression I only have the field Actuacion

The pivot table with all dimensions expanded looks like this:

And when I contract it to the level of the last date it looks like this:

When I contract the table at this level I would like to show the text of the field Actuacion related to the last date, how can do it? I'll thank you very much if you could help me to get it

I attach a file with the sample

Best Regards

Tomás

Guardar

Re: Pivot table with calculated dimension and expression depending of contract level

adamdavi3s — Mon, 14 Nov 2016 08:22:25 GMT

I've only had time to have a quick play and I don't have the answer I am afraid but you could try and get something like this working!

=if(Dimensionality() =2 ,only({<Fecha ={'$(=aggr(Max(Date(Fecha)),Cliente))'}>}Actuacion), Actuacion)

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: Pivot table with calculated dimension and expression depending of contract level

antoniotiman — Mon, 14 Nov 2016 08:37:14 GMT

Hi Tomas,

maybe

Expression :

FirstSortedValue(Actuacion,-Fecha)

Regards,

Antonio

Re: Pivot table with calculated dimension and expression depending of contract level

Mon, 14 Nov 2016 09:52:52 GMT

Thank you very much Adam, your answer didn't work completely but it have been very helpfull know about dimensionality function and I'll play with it.

Re: Pivot table with calculated dimension and expression depending of contract level

Mon, 14 Nov 2016 09:54:55 GMT

Thank you very much Antonio, your solution works flawlessly. You've help me a lot.