Create date interval from two date fields

[Bob07]

Hi experts,

I am new to Qlik and I need your help. I have two dates fields as shown below. I need to create intervals of 6months below, 2-5 years, etc, and sum my sales based on these intervals. Can anyone help me please? Thank you. 

Start Date	End Date	Expected	expected sum of sales
10/08/2010	04/02/2011	Within 6 Months	40
15/02/2011	22/03/2022	Up to 2 years	80
05/07/2010	10/07/2011	2-5 Years	30
11/05/2009	15/07/2013	Above 5 years	90
10/03/2010	07/05/2012
12/09/2013	20/06/2017

[AndyC]

I am sure that if I understand the request I could 😀

Can you explain what the table you've shown represents?

Is the the output you want to see or the calculations you want to perform?

[Bob07]

Hi AndyC,

The table represents two date fileds on my data. I want to create buckets of intervals based on the date fields and sum my sales for the different buckets. I would like to see both the calculations and output if possible. Thanks. 

[jelzo_nl]

Here is a code example how to match sales on a sales expectation:

// Create expected table
Expected:
NoConcatenate
LOAD RowNo() AS %ExpectedId,     
    Date#([Start Date],'DD/MM/YYYY') AS [Start Date],
    Date#([End Date],'DD/MM/YYYY') AS [End Date],
    [Period],
    Num#([Expected sum of sales]) AS [Expected sum of sales];
LOAD * INLINE [
    Start Date	End Date	Period	Expected sum of sales 
    10/08/2010	15/02/2011	Period 1	40
    15/02/2011	22/03/2022	Period 2	80
    22/03/2022	31/12/9999	Period 3	30
] (delimiter is '\t');

// Create a table connecting expectation to dates
ExpectedDates:
NoConcatenate
LOAD 
    %ExpectedId,
    [Start Date]+IterNo()-1 AS %DateId
    WHILE [Start Date]+IterNo()-1 <= [End Date];
LOAD * RESIDENT Expected;

// Create Sales table
Sales:
NoConcatenate
LOAD *,
	Floor([Sales Date]) AS %DateId;
LOAD 
    Date#([Sales Date],'DD/MM/YYYY') AS [Sales Date],
    [Sum of sales];
LOAD * INLINE [
    Sales Date	Sum of sales
    10/11/2010	50
    15/11/2011	100
    10/11/2019	50
    10/11/2023	110
] (delimiter is '\t');

// Optional, add calendar

 
These are the calculations:

I do not really understand how you want to use your 'Expected' column. These text intervals are not something you can use in the script.