Qliksense: Calculate 14 days bucket from 1st day available in any column

[rahulgoyal1287]

Hello Community members,

Many thanks for your help. i am working on a visualisation which is based on dates & it should bucketize based on 14 days starting from minimum date available in any dimension.

Lets say if i have a date field which starts with 01-Jun-2018.. i need to bucketise it as following:

starting from 1st day(minimum) to next 14 days

then next 14 days starting from next day.... and so on.

Date

01/06/2018 - 14/06/2018

15/06/2018 - 28/06/2018

29/06/2018 - 12/07/2018

13/07/2018 - 26/07/2018

27/07/2018 - 09/08/2018

10/08/2018 - 23/08/2018

24/08/2018 - 06/09/2018

07/09/2018 - 12/09/2018

Is there any way to do it? Please share it with me. It will be a great help!!

Best Regards,

Rahul Goyal

[niclaz79]

Hi,

Check out the function Class() where you can define buckets in your dimensions.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ConditionalFunctions/c...

[raman_rastogi]

Try Class Function in Chart Dimension.

ex- Class(Date,14)

[rahulgoyal1287]

Thanks Niclas for sharing the article.

based on the study, i have implemented the aggregation as following:

class(DATE1,14) as Date_Group

it is working however output comes as following (dates are populating but not in correct format). Is there any way to apply the correct format "DD/MM/YYYY" ?

Many Thanks

Rahul Goyal

[rahulgoyal1287]

Thanks for the response Raman. Could you please also help if you know how to resolve the above issue. Rahul

[niclaz79]

HI, 

Consider it a string that you can separate with Subfield() and put date() around it to convert to your preferred date format.

[raman_rastogi · Accepted solution]

try this

=date(subfield(class(Date,4,'x'),'<= x <',1),'DD/MM/YYYY') &'<= x <' &date(subfield(class(Date,4,'x'),'<= x <',2),'DD/MM/YYYY')

[OmarBenSalem]

Create it in the script as follow:

date(subfield( Class(date,5),'<=',1))&' - '&date(subfield( Class(date,5),'< ',2)) as interval

[nsetty]

Rahul, can you try below

class(DATE(DATE1,'DD/MM/YYYY'),14) as Date_Group

[OmarBenSalem]

won't work 😕