Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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.
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.