Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ilanbaruch
Specialist
Specialist

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
Gysbert_Wassenaar

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
Gysbert_Wassenaar

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
Specialist
Specialist
Author

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

Gysbert_Wassenaar

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
Specialist
Specialist
Author

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

Gysbert_Wassenaar

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
Specialist
Specialist
Author

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?


Gysbert_Wassenaar

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
Specialist
Specialist
Author

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

Gysbert_Wassenaar

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)


talk is cheap, supply exceeds demand