Count duration ranges out of start/end date

[#1]

I am quite new with Qlik and checked out a couple of things and the forum for further help, but was not able to get further.

I got a table with a couple of entries including a start and end date per entry in the format DD/MM/YY. So I changed the data editor to

SET DateFormat='DD/MM/YY';

but now I am stuck. I need to count the entries in groups of <6 month (or ~180 days), >6 month and 1 year (or ~360 days) and the rest. This should be printed in a pie-chart.

After trying a couple of soultions I even don't manage to print down the results in a table and I am not sure whether a correct day duration is being calculated

[sunny_talwar]

Would you be able to provide a sample with the expected output?

[#3]

Hi Sunny,

my table includes "Start Date" and "End Date". The "Duration" is just listed as an example. The Output should list the number of

Start Date	End Date	Duration
01.10.2016	30.09.2020	1460
01.10.2016	31.03.2017	181
01.08.2014	30.09.2017	1156
08.08.2016	17.10.2016	70
01.09.2016	30.09.2020	1490
01.10.2016	30.09.2020	1460
01.10.2016	30.09.2020	1460
01.10.2016	30.09.2020	1460
01.10.2016	31.03.2017	181
01.10.2016	30.09.2020	1460

• < 6 month (<180d): 1
• > 6 month (>180d): 2
• > 12 month (>360d): 7

-> into a pie chart

I hope this information helps

Later on I need to include a monthly forecast. But first of all i need to manage step first

[sunny_talwar]

May be like this where you create the ranges in the script:

Table:

LOAD *,

  If(Duration < 180, Dual('<180d', 1),

  If(Duration < 360, Dual('>180d', 2), Dual('>360d', 3))) as Range;

LOAD [Start Date],

    [End Date],

    [End Date] - [Start Date] as Duration

FROM

[https://community.qlik.com/thread/236251]

(html, codepage is 1252, embedded labels, table is @1);