topic Re: Need to add values based on before two dates in QlikView

Need to add values based on before two dates

praveenkumar_s — Wed, 25 Nov 2020 16:16:04 GMT

Hi friends,

I have date and cost column. all i need is just one more field which consists of total cost for current day + last two days in load script.

Please find the attached screenshot below.

Please let me know in case of any clarifications.

Re: Need to add values based on before two dates

shiveshsingh — Thu, 05 Apr 2018 17:11:34 GMT

use this expression

=rangesum(sum(Cost),Above(Cost,1),Above(Cost,2))

Re: Need to add values based on before two dates

eduardo_dimperio — Thu, 05 Apr 2018 17:11:46 GMT

Hi Kumar,

Use Peek(Total)+Cost AS TOTAL

Re: Need to add values based on before two dates

shiveshsingh — Thu, 05 Apr 2018 17:13:02 GMT

Re: Need to add values based on before two dates

praveenkumar_s — Thu, 05 Apr 2018 17:31:50 GMT

Thanks. but i just want that in load script. i want to add more field named total in load script.

Re: Need to add values based on before two dates

praveenkumar_s — Thu, 05 Apr 2018 18:12:41 GMT

Hi Eduardo,

Total you are referring inside peek function is the column i just want to create.

i want it in load script . so only i can generate charts.

Re: Need to add values based on before two dates

eduardo_dimperio — Thu, 05 Apr 2018 18:15:55 GMT

Peek() is a script function

Re: Need to add values based on before two dates

praveenkumar_s — Thu, 05 Apr 2018 18:19:56 GMT

yes i agree. but i just want to know what is the field "total" you used inside peek function?

and also i need to get the addition of above two rows only.

Re: Need to add values based on before two dates

eduardo_dimperio — Thu, 05 Apr 2018 18:41:22 GMT

Ah, ok...Try this

Peek(Cost,1)+Peek(Cost,2) AS TOTAL

Re: Need to add values based on before two dates

boorgura — Thu, 05 Apr 2018 19:41:50 GMT

Please try the below:

load *, rangesum(Cost, peek(Cost, -1), peek(Cost,-2)) as Total;

LOAD * Inline [

Date, Cost

2/2/2018 , 10

2/3/2018 , 20

2/4/2018 , 50

2/5/2018 , 60

2/6/2018 , 70

2/7/2018 , 20

2/8/2018 , 50

2/9/2018 , 60

2/10/2018 , 90

2/11/2018 , 15

2/12/2018 , 20

2/13/2018 , 25

];

Re: Need to add values based on before two dates

shiveshsingh — Fri, 06 Apr 2018 06:12:17 GMT

Use this bro

F:load Date, Cost, rangesum(Cost,Peek(Cost) ,Peek(Cost,-2)) as Total

Re: Need to add values based on before two dates

eduardo_dimperio — Fri, 06 Apr 2018 12:52:53 GMT

Well you've 3 answer using Peek(), just check one of then and mark the right answer pls

Re: Need to add values based on before two dates

praveenkumar_s — Mon, 09 Apr 2018 15:39:01 GMT

thanks for your continuous suggestion. finally i got that..

Re: Need to add values based on before two dates

praveenkumar_s — Mon, 09 Apr 2018 15:39:51 GMT

suppose if we want to add n date (eg- last 8 dates) means, how can we achieve that simply?