3, 6, 12 Month Bucket

[ks20]

Hi Community, 

I'm looking to set up three distinct timeframes: a 3-month bucket, a 6-month bucket, and a 12-month bucket. The challenge is that I have a field labeled [Date_Field], and I want these buckets to be defined in a way that they always span from the earliest date in the [Date_Field] to the latest date.

Let me know you thoughts on this, thank you!

[zfonline7888]

//The Period_Bucket is dynamicly updated/refreshed on the current date.  You can try the following scripts:

SET vToday = Today();
SET vTodayNum = Num(Date#(Today()));
SET v3MonthAgo = AddMonths(MonthStart(Today()),-3,1);
SET v3MonthAgoNum = Num(Date#(AddMonths(MonthStart(Today()),-3,1)));
SET v6MonthAgo = AddMonths(MonthStart(Today()),-6,1);
SET v6MonthAgoNum = Num(Date#(AddMonths(MonthStart(Today()),-6,1)));
SET v12MonthAgo = AddMonths(MonthStart(Today()),-12,1);
SET v12MonthAgoNum = Num(Date#(AddMonths(MonthStart(Today()),-12,1)));

Load *
    , If([Date_Field_Num]>$(v3MonthAgoNum) and [Date_Field_Num]<$(vTodayNum), Dual('3_Months_Bucket',1),
        If([Date_Field_Num]>$(v6MonthAgoNum) and [Date_Field_Num]<$(vTodayNum), Dual('6_Months_Bucket',2),
          If([Date_Field_Num]>$(v12MonthAgoNum) and [Date_Field_Num]<$(vTodayNum), Dual('12_Months_Bucket',3), Dual('Earlier',4)))) as [Period_Bucket]
;
Load *
    , MonthStart([Date_Field]) as [Month_Start]
    , MonthEnd([Date_Field]) as [Month_End]
    , Month([Date_Field]) as [Month_Name]
    , Num(Date#([Date_Field])) as [Date_Field_Num]
;
load *
Inline
[
Date_Field, Sales
2023-01-15, 100
2023-08-15, 200
2023-07-15, 300
2023-02-15, 400
2022-09-15, 500
2023-03-15, 600
2001-01-15, 700
]
;

[ks20]

I tried using the below expression:

=if((num(month(date)))<=3,'3 months',if((num(month(date)))<=6,'6 months','12 months'))

But the output is not relevant, for 3 months its shows

For 6 and 12 months, it does not give correct output, it should be showing 1 - 6 months and  1 - 12 months

[Gabbar]

Defining it in the script using If wont work because it wont reach else Condition and would be there in If condition.

So to solve this you might use:
3 Buttons- Giving them action to select Datefield in required bracket.
using variable might also work where you variable value affects the Datefield.

[deepanshuSh]

Hi @ks20  Could you elaborate more on the earliest date field, how exactly do you need the dates to be used.

if You just want the buckets to be dynamic as in there would correspond to the 3, 6, and 12 months respectively based on the current date or max date of the date field, then this could be the approach. 

addmonths(max(date_field),-6) as six months,

addmonths(max(date_field),-12) as 12 months.

[ks20]

So logically it should be current year months, so filter for 3,6 and 12 months. When select 3 months it should select current month first 3 months and so on..

[ks20]

Thanks for your response, not as per my requirement but can give it a try!

I need the buckets in one field.

[Gabbar]

So try this Change your If Function to in load Script

Table:
Load date,
if((num(month(date)))<=3,'3 months,6 months,12 months',if((num(month(date)))<=6,'6 months,12 months','12 months')) as MonthBracket;

noconcatenate
Table2:
load date, Subfield(MonthBracket,',') as Bracket resident Table;

Drop table Table;

(Dont do this in your fact table as there will be very high amount of data in that table, Keep the Table2 linked to fact table on the linking field date)

[Aditya_Chitale]

@ks20 

you will have to create calendar tables for each bucket, then concatenate them and link the concatenated table with your fact table on basis of date field.

Take a look at below script I tried using sample data. Hope this is what you were looking for:

Sample data:

Fact_Table:
load *,
dates as FactTableDates;
load * inline
[
dates
01/01/2023
02/01/2023
03/01/2023
04/01/2023
05/01/2023
06/01/2023
07/01/2023
08/01/2023
09/01/2023
10/01/2023
11/01/2023
12/01/2023
];

Step 1: Storing earliest and bucket wise future dates in variables:

for 3 months:

NoConcatenate

[3month]:

load

min(dates) as CurrentDate,

addmonths(min(dates),2) as Prev3Months

Resident Fact_Table;

let v3MCurrent = peek('CurrentDate',0,'3month');

let v3MPrev3month = peek('Prev3Months',0,'3month');

drop table [3month];

for 6 months:

NoConcatenate

[6month]:

load

min(dates) as CurrentDate,

addmonths(min(dates),5) as Prev6Months

Resident Fact_Table;

let v6MCurrent = peek('CurrentDate',0,'6month');

let v6MPrev6month = peek('Prev6Months',0,'6month');

drop table [6month];

for 12 months:

NoConcatenate

[12month]:

load

min(dates) as CurrentDate,

addmonths(min(dates),11) as Prev12Months

Resident Fact_Table;

let v12MCurrent = peek('CurrentDate',0,'12month');

let v12MPrev12month = peek('Prev12Months',0,'12month');

drop table [12month];

Step 2: creating bucket wise calendar tables:

NoConcatenate

Calendar_3M:

Load

'$(v3MCurrent)' + iterno()-1 as Num,

    Date('$(v3MCurrent)' + iterno()-1) as dates,

    '3 Month' as Flag

    autogenerate 1 while '$(v3MCurrent)' + iterno()-1 <= '$(v3MPrev3month)';

Concatenate (Calendar_3M)

Calendar_6M:

Load

'$(v6MCurrent)' + iterno()-1 as Num,

    Date('$(v6MCurrent)' + iterno()-1) as dates,

    '6 Month' as Flag

    autogenerate 1 while '$(v6MCurrent)' + iterno()-1 <= '$(v6MPrev6month)';

Concatenate (Calendar_3M)

Calendar_12M:

Load

'$(v12MCurrent)' + iterno()-1 as Num,

    Date('$(v12MCurrent)' + iterno()-1) as dates,

    '12 Month' as Flag

    autogenerate 1 while '$(v12MCurrent)' + iterno()-1 <= '$(v12MPrev12month)';

Output:

when 3 month bucket selected:

when 6 month bucket selected:

when 12 month bucket selected:

Regards,

Aditya