Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
craig157
Creator II
Creator II

Comparing Data over Several Months Issue

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:

IdAssetIdVolumeTypeVolumeReadDateVolumeCount
7005B82F-277F-4A33-88C9-8B81EDCA0E6F08678C88-A544-426A-82A3-64FB157C729ATotal Impressions31/01/20161832
80065A54-CBC7-4527-83E2-5A1D0B0B13A008678C88-A544-426A-82A3-64FB157C729ATotal Impressions28/02/201621339
481044CA-F35A-43DD-B34D-402FB0834C5208678C88-A544-426A-82A3-64FB157C729ATotal Impressions31/03/201641460
4524C0F1-626F-4EAD-A6A5-E24D8ED7AE2708678C88-A544-426A-82A3-64FB157C729ATotal Impressions27/04/201656704
0E192A41-070F-4EC7-8531-371C9703821B0A6FCB8A-CB59-4439-8695-A9E9B0DA969ETotal Impressions31/12/20155232
79EA47AC-529A-4DF5-B2A5-DBADB7772A750A6FCB8A-CB59-4439-8695-A9E9B0DA969ETotal Impressions08/01/20165240
C25A300D-D641-4A3D-9575-ACBBF03635740EB4FE2D-1250-48AF-8353-D4EB12071C06Total Impressions22/12/201589021
751C4D82-9E63-4234-9C7A-DFF78A1EECED0EB4FE2D-1250-48AF-8353-D4EB12071C06Total Impressions31/01/201690583
666673B9-58DC-4B04-8072-7D7A1AF882FD0EB4FE2D-1250-48AF-8353-D4EB12071C06Total Impressions29/02/201692164
38D02F4D-6F88-4CC6-899F-9B50971FB46F0EB4FE2D-1250-48AF-8353-D4EB12071C06Total Impressions31/03/201693544
FD5E6D83-2B84-4D07-8FA7-081A3511BFAB0EB4FE2D-1250-48AF-8353-D4EB12071C06Total Impressions29/04/201694788

 

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

 

Labels (5)
1 Solution

Accepted Solutions
craig157
Creator II
Creator II
Author

Found the solution was to utilise the Above Function.

 

Volume - above(TOTAL sum(Volume))

 

This has fixed all the problems 🙂

View solution in original post

2 Replies
lblumenfeld
Partner Ambassador
Partner Ambassador

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
Creator II
Creator II
Author

Found the solution was to utilise the Above Function.

 

Volume - above(TOTAL sum(Volume))

 

This has fixed all the problems 🙂