Pivot table with calculated dimension and expression depending of contract level

[#1]

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

[adamdavi3s]

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.

[antoniotiman · Accepted solution]

Hi Tomas,

maybe

Expression :

FirstSortedValue(Actuacion,-Fecha)

Regards,

Antonio

[#4]

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.

[#5]

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