Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kennytang
New Contributor

Find latest status and sum value

Hi All,

I have a table below, what i want is to get the updated status of of users and sum all those values and do it in function , any suggestion ?

UserID    Date         Status           Value

A         13/2/2017     Status_A         100

A         13/3/2017     Status_A         100

A         13/6/2017     Status_B         200

A         13/7/2017      Status_B         300

A         13/12/2017    Status_B         400                 (So sum all Status_B for A)

B         13/1/2017     Status_C          1

B         13/2/2017     Status_C          2

B         13/12/2017    Status_D         4                   

The expected result

UserID      Status        Sum

A         Status_B      900    (200+300+400)  

B         Status_D      4  

Thanks in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: Find latest status and sum value

May be this

Dimension

UserID

Expression

FirstSortedValue( Status, - Date)

Sum(Aggr(If(Status = FirstSortedValue(TOTAL <UserID> Status, - Date), Value), UserID, Status, Date))

5 Replies
MVP
MVP

Re: Find latest status and sum value

May be this

Dimension

UserID

Expression

FirstSortedValue( Status, - Date)

Sum(Aggr(If(Status = FirstSortedValue(TOTAL <UserID> Status, - Date), Value), UserID, Status, Date))

kennytang
New Contributor

Re: Find latest status and sum value

thank you for your help, but it seems not working, it give me 0.

kennytang
New Contributor

Re: Find latest status and sum value

thank you for your help, but it seems not working, it give me 0.

I want to show it in bar chart actually,

so that the the chart show below

Status B     Status D

MVP
MVP

Re: Find latest status and sum value

Not sure I understand... what is your bar chart dimension? Status?

kennytang
New Contributor

Re: Find latest status and sum value

imy mistake, it works, thxSunny

Community Browser