Get the latest value for each department

[#1]

Hi Guys

I created three Excel(sale1, sale2, sale3), each one contains every department's sales value.

First, I need to load these Excel to QV one by one with script.

Second, I need to get the latest value from each department.

The latest value means the last time I imported to QV, as in bellow's screenshot,

the No.3 group means the latest value.

And you can see that, in the original Excel, there's no timestamp for each department sales data.

So I created one column named Key in QV marked as a timestamp in order to distinguish them.

Once the Key was created, I can achieve my purpose, just obtain the maximum Key number for each department,

then the latest value is easy to get.

However, after I finished the expression, the chart display nothing.

I'm not sure what is going on with my expression, does someone could help me out?

Thanks a lot.

[Anil_Babu_Samineni]

May be you need this?

FirstSortedValue(Value, Department) as FirstValue

[ahaahaaha · Accepted solution]

Hi,

May be like as at attached file?

Regards,

Andrey

[antoniotiman]

Hi,

Dimension : Department

Expression : FirstSortedValue(Value,-Key)

Regards,

Antonio

[#5]

Hi Andrey

It works, quite thanks.

I check your expression:

Key={"$(=FirstSortedValue({<Department={'TMS1'}>} Key, -Key))"}

Could you help to tell me what does this statement mean?