topic Re: Value with last update in QlikView

Value with last update

antonaks — Wed, 25 Nov 2020 16:16:04 GMT

Hello Colleagues!

I have an issue, and can't solve it.

For example, I have data like in table:

Client	Date	Update time	Result
AAA	03-05-18	14:30	910.29
AAA	03-05-18	13:25	181.11
AAA	03-05-18	12:10	616.89
AAA	02-05-18	15:40	890.93
AAA	02-05-18	12:30	25.52
BBB	03-05-18	19:48	969.58
BBB	03-05-18	15:08	759.28
BBB	03-05-18	13:34	276.29
BBB	03-05-18	10:22	493.25
BBB	02-05-18	12:10	657.09
BBB	02-05-18	15:40	516.63
CCC	03-05-18	14:30	496.91
CCC	02-05-18	13:25	499.3
CCC	02-05-18	12:10	932.85

I what create expression with set analysis which will get values in latest update time, but group by client and day.

I know how it make in load script, but I need set analysis expression for use it in chart.

Final result should be like this -

Final result
AAA	03-05-18	14:30	910.29
AAA	02-05-18	15:40	890.93
BBB	03-05-18	19:48	969.58
BBB	02-05-18	15:40	516.63
CCC	03-05-18	14:30	496.91
CCC	02-05-18	13:25	499.3

Thanks for any help.

Re: Value with last update

maxgro — Sat, 05 May 2018 13:16:09 GMT

in a straight table chart

- add 2 dimensions, Client, Date

- add 2 expression

max([Update time])

FirstSortedValue(Result, -[Update time])

Re: Value with last update

Anil_Babu_Samineni — Sat, 05 May 2018 13:18:08 GMT

Check this?

Create Table

Dimensions - Client and Date

Expressions are

1) Time(Max([Update time]),'hh:mm')

2) FirstSortedValue(Result, -[Update time])

Re: Value with last update

sasiparupudi1 — Sat, 05 May 2018 13:19:40 GMT

expression

Time(Max(Aggr(Max([Update time]),Client,Date)),'h h:mm')

Re: Value with last update

antonaks — Mon, 07 May 2018 07:35:25 GMT

Hello everybody. Thank you for help. I'm sorry but I forgot mention one special thing.

In final table I need compare last update value with previous in the same day.

Re: Value with last update

jyothish8807 — Mon, 07 May 2018 11:27:29 GMT

Hi Anton,

Try this:

- add 2 dimensions, Client, Date

- add 4 expression

max([Update time])

FirstSortedValue(Result, -[Update time])

max([Update time],2)

FirstSortedValue(Result, -[Update time],2)

Br,

Re: Value with last update

jyothish8807 — Mon, 07 May 2018 11:29:09 GMT

Pfa

Re: Value with last update

antonaks — Thu, 10 May 2018 08:03:22 GMT

Thank you very much!

Re: Value with last update

jyothish8807 — Thu, 10 May 2018 08:08:26 GMT

You are welcome

Re: Value with last update

antonaks — Thu, 10 May 2018 09:37:18 GMT

I'm sorry.

Maybe you can help me again.

I still have problem with FirstSortedValue function.
Expression returned sum() for only one Client. I think I should use Aggr function, but I don't know how to input it in my expression.

Re: Value with last update

jyothish8807 — Thu, 10 May 2018 10:13:00 GMT

Hi Anton,

You do not require sum() function in this case, simply use first sorted value as mentioned in above example.

Br,

Re: Value with last update

antonaks — Thu, 10 May 2018 10:56:31 GMT

But I have another dimensions in main table. Like Dates and Accounts.

If I will use only "first sorted value" I will get only one value, but I need sum().

Re: Value with last update

antonaks — Thu, 10 May 2018 13:37:03 GMT

I can share a full data table if it helps.

Re: Value with last update

jyothish8807 — Mon, 14 May 2018 04:31:39 GMT

Hi Anton,

Yes, that will be really helpful along with the expected output

Br,