Banded Values

[DaveStraw]

Hi, 

I am trying to make a banded value for a table column.

For example, using a date field to create a column with value 01/01/2025 - 07/01/2025 and the next value in the row below being 08/01/2025 - 14/01/2025 etc

I got as far a using case in the column: =Class(Date([DateField],'DD-MM-YYYY'),7) but this is giving me values of 44975 <= x < 44982 and the next row a value of 44982 <= x < 44989 etc

What my ultimate aim is to create dynamic banding so I can create my own banding values to be used in the table. 

If anyone is able to point me in the right direction that would be great, 

Thanks in advance.

[CoenP_80]

Hi,

You can try the following, either in script or in your table:

Date(floor(([Datefield]-45658)/7)*7 +45658) & ' - ' & Date(floor(([Datefield]-45658)/7)*7 +45664) ,

45658 being the numerical value of your starting point (01/01/2025) and 45664 being the end date of the first banded value

[DaveStraw]

Hi, 

Thanks for the quick reply, it looks like it's working but I'm just checking a few things out to make sure the right data is being brought back.

Can I also ask - how do I sort the date in the table in descending order as at the moment it's showing all dates in numeric order. For example 01/03/2023 then next row 01/05/2024  and then 01/11/2023 and then 02/08/2023 etc 

Thanks again 

[marcus_sommer]

I think I would do it in this way:

dual(date(subfield(class([Date], $(myVar)), ' ', 1)) &
        ' <= x < ' &
        date(subfield(class([Date], $(myVar)), ' ', 5)),
        class([Date], $(myVar)))

with a variable for the bucket-size.

[Chanty4u]

Try this 

='From ' & Date(Class([DateField], $(vBandWidth))) & ' to ' & Date(Class([DateField], $(vBandWidth)) + $(vBandWidth) - 1)