Account Value Analysis

[ilanbaruch]

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?

days	1	2	3	4	5	6	7	8
user no.
1	100	90	50	200	35	50	250	100
2	500	30	330	84	74	100	230	20
3	250	800	750	660	84	32	20	10

Thank you,

Ilan

[Gysbert_Wassenaar]

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

[ilanbaruch]

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.

[ilanbaruch]

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

[ilanbaruch]

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;

[ilanbaruch]

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 · Accepted solution]

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)