Comparing Data over Several Months Issue

[craig157]

Hi All,

We moved from reading data from CSVs that were ordered by Filename to now reading straight from a SQL table.

I have a table like below:

Id	AssetId	VolumeType	VolumeReadDate	VolumeCount
7005B82F-277F-4A33-88C9-8B81EDCA0E6F	08678C88-A544-426A-82A3-64FB157C729A	Total Impressions	31/01/2016	1832
80065A54-CBC7-4527-83E2-5A1D0B0B13A0	08678C88-A544-426A-82A3-64FB157C729A	Total Impressions	28/02/2016	21339
481044CA-F35A-43DD-B34D-402FB0834C52	08678C88-A544-426A-82A3-64FB157C729A	Total Impressions	31/03/2016	41460
4524C0F1-626F-4EAD-A6A5-E24D8ED7AE27	08678C88-A544-426A-82A3-64FB157C729A	Total Impressions	27/04/2016	56704
0E192A41-070F-4EC7-8531-371C9703821B	0A6FCB8A-CB59-4439-8695-A9E9B0DA969E	Total Impressions	31/12/2015	5232
79EA47AC-529A-4DF5-B2A5-DBADB7772A75	0A6FCB8A-CB59-4439-8695-A9E9B0DA969E	Total Impressions	08/01/2016	5240
C25A300D-D641-4A3D-9575-ACBBF0363574	0EB4FE2D-1250-48AF-8353-D4EB12071C06	Total Impressions	22/12/2015	89021
751C4D82-9E63-4234-9C7A-DFF78A1EECED	0EB4FE2D-1250-48AF-8353-D4EB12071C06	Total Impressions	31/01/2016	90583
666673B9-58DC-4B04-8072-7D7A1AF882FD	0EB4FE2D-1250-48AF-8353-D4EB12071C06	Total Impressions	29/02/2016	92164
38D02F4D-6F88-4CC6-899F-9B50971FB46F	0EB4FE2D-1250-48AF-8353-D4EB12071C06	Total Impressions	31/03/2016	93544
FD5E6D83-2B84-4D07-8FA7-081A3511BFAB	0EB4FE2D-1250-48AF-8353-D4EB12071C06	Total Impressions	29/04/2016	94788

I need to compare the Volumes over months (so the day of the month could depend on the readings received)

For example:

Date: 29/04/16 Read: 94788

Date: 31/03/16 Read: 93544

So April Volume is therefor 1,244

I was trying the below but I believe it's not working because the SQL database will just look at Row number instead, there is no order to how the data is populated.

if(Previous([Volume Type]) = [Volume Type] AND
Previous([Serial Number]) = [Serial Number] AND
Previous(Date([Volume Read Date]))<Date([Volume Read Date]) ,
[Volume Count] - Previous([Volume Count])) as Volume,

When I import the data I Order it by AssetId, VolumeType  and VolumeReadDate asc;

Any suggestions would be appreciated!

Cheers

Craig

[lblumenfeld]

Your statement "When I import the data I Order it by AssetId, VolumeType  and VolumeReadDate asc;"

Does this mean that you are ordering it in the load statement, or is this your Order By clause in the SQL query (from your database)?

[craig157 · Accepted solution]

Found the solution was to utilise the Above Function.

Volume - above(TOTAL sum(Volume))

This has fixed all the problems 🙂