Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
stan1
Contributor
Contributor

Set analysis for summing volume within a date and time range.

Hi All,

I am trying to create a table that sum a list of numbers based on date range and group them by Year-Month.  The business rule is to sum the volume for each month where the start date begins at 7 AM of the month and the end date is at 7 AM the first day next month. 

Below is the raw data:

Start DateTimeEnd DateTimeVolume
08/01/2019 08:00:00 AM08/05/2019 06:00:00 AM30
08/20/2019 09:40:01 PM08/31/2019 12:00:00 AM50
08/21/2019 09:06:00 AM09/01/2019 07:00:00 AM10
09/01/2019 07:00:01 AM09/20/2019 10:00:50 PM20
09/22/2019 11:00:00 PM09/30/2019 07:00:00 AM30
10/01/2019 02:00:00 AM10/01/2019 07:00:00 AM10

 

Below is the table that I would like the data to be presented.

Year-MonthTotal Volumes
2019-Aug90
2019-Sept60

 

I have the following set analysis for the Total Volumes column but it is not summing quite right if I look at the whole year:

 

aggr(Sum({(<EndDateTime={"<=$(=Date(Floor(MonthStart(MAX(Floor(StartDateTime)))) + 0 + (7/24), 'M/DD/YYYY hh:mm:ss TT'))"}> * (<StartDateTime={">=$(=Date(Floor(MonthStart(MIN(Floor(StartDateTime)))) + (7/24), 'M/DD/YYYY hh:mm:ss TT'))"}> + <StartDateTime={">=$(=Date(Floor(AddMonths(MonthStart(MIN(StartDateTime)), 1)) + (0/24), 'M/DD/YYYY hh:mm:ss TT'))"}>))} Volume), StartDateTime.autoCalendar.YearMonth])

 

 

0 Replies