How to create a bucketlist based on Date ?

Steelbird

Hi Team,@Jerry Casey

How to create bucket list in report based on date column? Below is the sample date.Yellow one is bucket list weeks and blue color is date column and other one is measure column.
To build Bucket list based on date column and display up-to 12 weeks and rest will go to the 13week.

Thank you,

Steel.

Jerry Casey

Do you have a time dimension associated with this date field? One that contains week-of-year?  If so the pivot table would be pretty straightforward to duplicate your illustration.

Otherwise, you can do some timestamp gymnastics to bin the dates into the Saturday date labels for the weeks:

case dayofweek("Contract"."Award Date") when 1 then TIMESTAMPADD(SQL_TSI_DAY,6,"Contract"."Award Date") when 2 then TIMESTAMPADD(SQL_TSI_DAY,5,"Contract"."Award Date") when 3 then TIMESTAMPADD(SQL_TSI_DAY,4,"Contract"."Award Date") when 4 then TIMESTAMPADD(SQL_TSI_DAY,3,"Contract"."Award Date") when 5 then TIMESTAMPADD(SQL_TSI_DAY,2,"Contract"."Award Date") when 6 then TIMESTAMPADD(SQL_TSI_DAY,1,"Contract"."Award Date") else "Contract"."Award Date" end

Steelbird

Hi @Jerry Casey, I have misinterpreted the logic. For Example If Award Date is 1/9/2020 the week data 5766239.14 value should fall under 5 - Jan Bucket which is a Sunday Label.(It should fall under Previous Bucket). The logic should be implemented with Current_date & Award Date.I want to display only 12 weeks from Current date + 13th week where the rest of the values fall in 13th week bucket.

Thank you,

Steel.

Jerry Casey

I'm not clear on what you mean by "The logic should be implemented with Current_date & Award Date".  If you mean that you want the 12 weeks preceding the current date, and all others go into a 13th column, then it would look like this: (I'm calling the all other column 'Earlier')

case when "Contract"."Award Date" <TIMESTAMPADD(SQL_TSI_WEEK,-12,current_date) then 'Earlier' ELSE

EVALUATE('to_char(%1,%2)' AS CHAR, Case dayofweek("Contract"."Award Date") when 7 then TIMESTAMPADD(SQL_TSI_DAY,-6,"Contract"."Award Date")

when 6 then TIMESTAMPADD(SQL_TSI_DAY,-5,"Contract"."Award Date")

when 5 then TIMESTAMPADD(SQL_TSI_DAY,-4,"Contract"."Award Date")

when 4 then TIMESTAMPADD(SQL_TSI_DAY,-3,"Contract"."Award Date")

when 3 then TIMESTAMPADD(SQL_TSI_DAY,-2,"Contract"."Award Date")

when 2 then TIMESTAMPADD(SQL_TSI_DAY,-1,"Contract"."Award Date")

else "Contract"."Award Date" end,'DD-Mon') END

I used EVALUATE TO_CHAR because  it's easiest to get the 'DD-Mon' format

Then, you need a Sorting version of the same column:

case when "Contract"."Award Date" <TIMESTAMPADD(SQL_TSI_WEEK,-12,current_date) then date '2001-01-01' ELSE

Case dayofweek("Contract"."Award Date") when 7 then TIMESTAMPADD(SQL_TSI_DAY,-6,"Contract"."Award Date")

when 6 then TIMESTAMPADD(SQL_TSI_DAY,-5,"Contract"."Award Date")

when 5 then TIMESTAMPADD(SQL_TSI_DAY,-4,"Contract"."Award Date")

when 4 then TIMESTAMPADD(SQL_TSI_DAY,-3,"Contract"."Award Date")

when 3 then TIMESTAMPADD(SQL_TSI_DAY,-2,"Contract"."Award Date")

when 2 then TIMESTAMPADD(SQL_TSI_DAY,-1,"Contract"."Award Date")

else "Contract"."Award Date" end END

I have fewer than 13 columns because I don't have Award Dtaes in every week.

Steelbird

Hi @Jerry Casey, Thank you for the immediate response. I can able to achieve it from the above logic, The thing is that I did not have any filters on report but whenever a report executes it should display the Current_date + 12 weeks data + 13th week data.For suppose I just added Award Date greater than 12/4/19 which it should populate only preceding 12weeks data + 13th Week data.But in my case it is fetching all weeks records.It is not restricting to 12+13 weeks data.

Thank you,

Steel.

Jerry Casey

What does your query filter look like?

You can filter the query with this, or something like it:

"Contract"."Award Date" >TIMESTAMPADD(SQL_TSI_WEEK,-12,current_date)

Steelbird

H @Jerry Casey

For Example: I have date records from 2019 May to 2020 DEC.

There will be no filters in report. When I execute report it should pick Current date Mar-12-2020 and it should populate till 12 weeks(MAR-MAY results) + 13 week(Rest from May to till DEC) it should fall in 13th bucket.Before Mar-2020 (i.e 2019 May - Mar 2020) it should not display results.

In my case the report is populating all the week buckets till 2020 DEC.

Thank you,

Steel.

Jerry Casey

Hi Steel,

I don't have much experience working with future record dates, but it should just require variations on the logic I've used above.

You want to exclude all results prior to the current date, without any filters.  As far as I can see, that would require a flag column, such as CASE WHEN date < CURRENT_DATE THEN 'B' ELSE 'A' END.  Your pivot table would then use the 'A' value in the prompt field (which can be hidden from view).

The logic for your bucket would be something like :

case when "Contract"."Award Date" >TIMESTAMPADD(SQL_TSI_WEEK,12,current_date) then 'Beyond 12 Weeks' ELSE

EVALUATE('to_char(%1,%2)' AS CHAR, Case dayofweek("Contract"."Award Date") when 7 then TIMESTAMPADD(SQL_TSI_DAY,-6,"Contract"."Award Date")

when 6 then TIMESTAMPADD(SQL_TSI_DAY,-5,"Contract"."Award Date")

when 5 then TIMESTAMPADD(SQL_TSI_DAY,-4,"Contract"."Award Date")

when 4 then TIMESTAMPADD(SQL_TSI_DAY,-3,"Contract"."Award Date")

when 3 then TIMESTAMPADD(SQL_TSI_DAY,-2,"Contract"."Award Date")

when 2 then TIMESTAMPADD(SQL_TSI_DAY,-1,"Contract"."Award Date")

else "Contract"."Award Date" end,'DD-Mon') END

With similar changes to the sort column.