topic Re: Account Value Analysis in QlikView

Account Value Analysis

ilanbaruch — Sun, 04 Oct 2015 10:48:22 GMT

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

Re: Account Value Analysis

Gysbert_Wassenaar — Sun, 04 Oct 2015 15:12:47 GMT

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

Re: Account Value Analysis

ilanbaruch — Tue, 06 Oct 2015 12:10:19 GMT

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

Re: Account Value Analysis

Gysbert_Wassenaar — Tue, 06 Oct 2015 15:03:26 GMT

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.

Re: Account Value Analysis

ilanbaruch — Sun, 11 Oct 2015 14:08:49 GMT

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

Re: Account Value Analysis

Gysbert_Wassenaar — Sun, 11 Oct 2015 16:23:39 GMT

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

Re: Account Value Analysis

ilanbaruch — Mon, 12 Oct 2015 10:40:40 GMT

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?

Re: Account Value Analysis

Gysbert_Wassenaar — Mon, 12 Oct 2015 11:41:42 GMT

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;

Re: Account Value Analysis

ilanbaruch — Mon, 12 Oct 2015 13:18:04 GMT

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

Re: Account Value Analysis

Gysbert_Wassenaar — Mon, 12 Oct 2015 13:33:54 GMT

Ok, then use the firstsortedvalue:

firstsortedvalue(value, -date)

firstsortedvalue(value, -date,2)

Re: Account Value Analysis

ilanbaruch — Tue, 13 Oct 2015 09:36:10 GMT

Thank You.!!

Appreciate it a lot!