topic Re: Get the latest value for each department in QlikView

Get the latest value for each department

Wed, 25 Nov 2020 16:16:04 GMT

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 — Wed, 02 Aug 2017 07:14:02 GMT

May be you need this?

FirstSortedValue(Value, Department) as FirstValue

ahaahaaha — Wed, 02 Aug 2017 07:21:03 GMT

Hi,

May be like as at attached file?

Regards,

Andrey

antoniotiman — Wed, 02 Aug 2017 07:22:00 GMT

Hi,

Dimension : Department

Expression : FirstSortedValue(Value,-Key)

Regards,

Antonio

Wed, 02 Aug 2017 07:35:53 GMT

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?