Show current and last 3 Qtrs

[PA5]

Hi All,

I'm writing a formula for a bar graph which should show the current and last 3 quarters by default. But when the user selects Year, Quarter or Month from filters then the filters should function like normal.

I'm trying a formula which is not giving me any result, please let me know what I am doing wrong here.

    Count(

        Distinct

        {<

            prod_status_tm = {'Promoted'},

            call_date = {

                =If(

                    IsNull(GetFieldSelections([created_date.autoCalendar.Year])) and

                    IsNull(GetFieldSelections([created_date.autoCalendar.Quarter])),

                    '>=$(=Date(AddMonths(Today(), -12), ''MM/DD/YYYY''))<=$(=Date(Today(), ''MM/DD/YYYY''))',

                    '$(=GetFieldSelections([created_date.autoCalendar.Year]))',

                    '$(=GetFieldSelections([created_date.autoCalendar.Quarter]))'

                )

            }

        >}

        case_nmbr

    ),

    '#,##0'  // Format as a thousands separator

)

[rdm6532]

I would use a variable in your load script like this: 

LET vFourQuarterStartDate = floor(monthstart(addmonths(today(),-9)));

If you run this today (April 14, 2025), the value it would return is a 5 digit number that corresponds to the date July 1, 2024. Adjusting the -9 is the key to the formula.

You can then have a date in your data set like this: 

left(num(call_date),5) as numcall_date,

With these two things in your load script, you can make your set analysis as follows:

Count(Distinct {<prod_status_tm={'Promoted'},call_date={ '>=   }>}  case_nmbr)

[PA5]

Hi @rdm6532 

Thank you for your response. So I updated the formula to this:

=Count(Distinct {<Numcreated_date={">=$(=vFourQuarterStartDate)"}>} case_nmbr)

It does the the last few Qtrs as expected.. bt if I filter any previous years other than the last 4Qtrs. It does not return any value