# what is the Analytic function that I am searching for and how can I use it?

Vertica Customer

Hi Verticas!
I have finally reached out to the point that I actually need the analytic functions.
I am looking for but can't find the exact function I need.
Attached is a screen shot of my table but here is another example:
I need to add a column on the right that will do the calculation difference between amount of
"Sales" from one row to the amount of "Sales" of the following row:
E.g -
Lets say my query looks like this:

Catagory month+year Sales

Books 01/2020 100\$
Books 02/2020 150\$
Books 03/2020 220\$
Books 04/2020 280\$

I need to add another column on the right that will sum up the difference in Sales between one row to the other, so for the example above on the left to the "Sales" column I need a "Difference in sales" Column :

Difference

0\$
50\$
70\$
60\$

I added a screenshot of my real table if it was not clear...
I know there is a simple analytic function to use with OVER and Window .
Can someone tell me what it is exactly and how to use it?

Keren

1.JPG 87.1K
Tagged:

• - Select Field - Administrator
edited June 2021

Here is a super simple example:

```verticademos=> SELECT * FROM t;
Catagory | month+year | Sales
----------+------------+-------
Books    | 01/2020    |     0
Books    | 02/2020    |   150
Books    | 03/2020    |   220
Books    | 04/2020    |   280
(4 rows)

verticademos=> SELECT *, Sales - NVL(LAG(Sales, 1) OVER (PARTITION BY Catagory ORDER BY Catagory, "month+year"), 0) "Difference in sales" FROM t ORDER BY Catagory, "month+year";
Catagory | month+year | Sales | Difference in sales
----------+------------+-------+---------------------
Books    | 01/2020    |     0 |                   0
Books    | 02/2020    |   150 |                 150
Books    | 03/2020    |   220 |                  70
Books    | 04/2020    |   280 |                  60
(4 rows)
```

Might have to handle the ordering of the "month+year" a little better by maybe converting it to a date...

See doc page: LAG

• Vertica Customer

Worked like magic:-)) Thank you so so much