Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ilanbaruch
Contributor III

Account Value Analysis

Hi All,

I have a list of users and their value for every day want to compare between latest Value to previous value,

what would be the most efficient way to do it?

days12345678
user no.
110090502003550250100
250030330847410023020
325080075066084322010

Thank you,

Ilan

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)


talk is cheap, supply exceeds demand
10 Replies
MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

Use the above() or before() function, depending on the layout of your table/chart. Something like sum(Value)/before(sum(Value))-1


talk is cheap, supply exceeds demand
ilanbaruch
Contributor III

Re: Account Value Analysis

Hi Gysbert,

thank you for your reply,

to create a field that will hold the change between today() and today()-1

is it possible to do in in the script?

thank you,

Ilan

MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

Very likely. You will probably have to sort your table first and then you can use the previous() or peek() function to retrieve values from a previous record.


talk is cheap, supply exceeds demand
ilanbaruch
Contributor III

Re: Account Value Analysis

hi again,

the table structure is: user_id, date, value

can you explain how to use peek function to calculate ( latest value / previous value) per user?

appreciate your help

Ilan

MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

If the data is sorted by user_id first and by date second then you can use something like if(previous(user_id)=user_id,peek(value)) as previous_value


talk is cheap, supply exceeds demand
ilanbaruch
Contributor III

Re: Account Value Analysis

Hi,

I did it like this:

LOAD

account_id ,

date,

value,

if (previous(ACCOUNT_ID)=ACCOUNT_ID,Peek('value', -2)) as PreviousValue,

peek('value',-1) as CurrentValue

Resident TempAccountValue

Order by ACCOUNT_ID, date;

---

but I receive more than 2 values per each account, is there a way to load just the 2 last values?


MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

Ok, if you only want to top 2 values per account try this:

LOAD

     ACCOUNT, Max(value) as LargestValue, Max(value,2) as SecondLargestValue

Resident TempAccountValue

Group By ACCOUNT;


talk is cheap, supply exceeds demand
ilanbaruch
Contributor III

Re: Account Value Analysis

Hi,

I need the Latest Value  and the second Latest.. not the biggest

tried it also like:

LOAD

ACCOUNT_ID,

num(peek('value',-1,'ACCOUNT_ID')) as CurrentValue,

num(peek('value',-2,'ACCOUNT_ID')) as PreviousValue

//Max(value) as LargestValue,

//Max(value,2) as SecondLargestValue

Resident AccountValue

Group By ACCOUNT_ID;

did not work:(

any ideas

MVP & Luminary
MVP & Luminary

Re: Account Value Analysis

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)


talk is cheap, supply exceeds demand