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
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
Partner
Partner

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')

View solution in original post

10 Replies
Partner
Partner

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...

Partner
Partner

Try Class Function in Chart Dimension.

ex- Class(Date,14)

Contributor III
Contributor III

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" ?

Capture.PNG

Many Thanks

Rahul Goyal

Contributor III
Contributor III

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

Partner
Partner

HI, 

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

Partner
Partner

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')

View solution in original post

Partner
Partner

Create it in the script as follow:

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

Capture.PNG

Partner
Partner


Rahul, can you try below


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

Partner
Partner

won't work 😕