Text values of datamodel dates is not getting link to dual field of date

[Karim_Khan]

Hi Folks,

Previously have raised one thread regarding linking of existing dates (Text format) with Dual dates(Num) but i haven't found any solution for it.

Reposting it here :

I have recenlty created the date field using Dual to show the dates in descending order in filter pane and selection tool both and its working well.

Dual(Date1,-1*Date1) giving result 2025/10

While i tried to incorporate this changes into my existing dates i.e Date which is also holding dates in 2025/10 format but in Text format its not working.

Let say i wanted to show YTD and Rolling 12 months data when clicking on action button.Its wokring fine and getting associated and selecting with green color.

R12 Logic:

=year(addmonths(TODAY(),-2))&'/'&if(num(month(addmonths(TODAY(),-2)))<10,'0','')&num(month(addmonths(TODAY(),-2)))&';' &year(addmonths(TODAY(),-3))&'/'&if(num(month(addmonths(TODAY(),-3)))<10,'0','')&num(month(addmonths(TODAY(),-3)))&';' &year(addmonths(TODAY(),-4))&'/'&if(num(month(addmonths(TODAY(),-4)))<10,'0','')&num(month(addmonths(TODAY(),-4)))&';' &year(addmonths(TODAY(),-5))&'/'&if(num(month(addmonths(TODAY(),-5)))<10,'0','')&num(month(addmonths(TODAY(),-5)))&';' &year(addmonths(TODAY(),-6))&'/'&if(num(month(addmonths(TODAY(),-6)))<10,'0','')&num(month(addmonths(TODAY(),-6)))&';' &year(addmonths(TODAY(),-7))&'/'&if(num(month(addmonths(TODAY(),-7)))<10,'0','')&num(month(addmonths(TODAY(),-7)))&';' &year(addmonths(TODAY(),-8))&'/'&if(num(month(addmonths(TODAY(),-8)))<10,'0','')&num(month(addmonths(TODAY(),-8)))&';' &year(addmonths(TODAY(),-9))&'/'&if(num(month(addmonths(TODAY(),-9)))<10,'0','')&num(month(addmonths(TODAY(),-9)))&';' &year(addmonths(TODAY(),-10))&'/'&if(num(month(addmonths(TODAY(),-10)))<10,'0','')&num(month(addmonths(TODAY(),-10)))&';' &year(addmonths(TODAY(),-11))&'/'&if(num(month(addmonths(TODAY(),-11)))<10,'0','')&num(month(addmonths(TODAY(),-11)))&';' &year(addmonths(TODAY(),-12))&'/'&if(num(month(addmonths(TODAY(),-12)))<10,'0','')&num(month(addmonths(TODAY(),-12)))&';' &year(addmonths(TODAY(),-13))&'/'&if(num(month(addmonths(TODAY(),-13)))<10,'0','')&num(month(addmonths(TODAY(),-13)))

E.g 2025/10;2025/09;2025/08;...2024/11

[QFabian]

Hi @Karim_Khan , remember that texts are aligned to the left and numbers are aligned to the right, so your solution is working with texts, not with dates/numbers
Maybe one soution is getting all the dates in numbers, and make the selections with numbers, formatting your date to the expected date/format.
For example, 2025/10 is about the month 10 of the year 2025, and your formula is putting texts togheter (year and '/' and month), not creating the appropiate date to be considered as a number.
try something like this :

date(monthname(addmonths(TODAY(),-2)),'YYYY/MM')

[Chanty4u]

Hi 

As suggested by @QFabian is right but you can give below suggestions 

Align Both Fields During Load 

Create the Dual field from the same text field, so both share identical text and comparable numeric parts.

Example:

LOAD

    DateText, // existing field '2025/10'

    Dual(DateText, Num(Date#(DateText,'YYYY/MM'))) as MonthDual

Resident YourTable;

[Karim_Khan]

Hi,

If we use Dual(DateText, Num(Date#(DateText,'YYYY/MM'))) as MonthDual is not getting sorted when selecting in selection filter.Hence i have used

dual(DateText,-1 * Floor(MonthEnd(MakeDate(Num(Left(DateText, 4)),Num(SubField(DateText, '/', 2)),1)))) As Dual_Date

This was getting sorted correctly from newest to oldest in both filter and selection pane.But the problem comes when we start preparing R12 and YTD logic as per Dual_Date.

Regards,

KK

[Nagaraju_KCS]

your Action Button, use a Select in Field action on the Date field with this expression:

= {' >$(=Date(MonthStart(AddMonths(Today(), -13)))) <$(=Date(MonthEnd(AddMonths(Today(), -2)))) '}

[Karim_Khan]

The above expression will give values Error in Expression : ={'>'10/1/2024<09/30/2025'} whereas mydate is in YYYY/MM format which in text and Dual_date is also in YYYY/MM format whihc is Numeric type.I tried to pass the variable with action button for both dates by updating the format but no luck

=Concat(
DISTINCT
{<Dual_Date= {">=$(=MonthStart(AddMonths(Today(), -13)))<=$(=MonthEnd(AddMonths(Today(), -2)))"}>}
Date(Dual_Date, 'YYYY/MM'),
';'
)

[Karim_Khan]

Well I have got the intermediate solution to this approach after trying below efforts in script.

After loading this data i have crated the button which will use Select Variable R12 Inception='Yes' and added one more action to select All Dual date in that way my months are appearing in sorted order and selected correctly.

Note : Only issue is that the label in Selection Pane in coming As 'All' rest all work.

Sort_Dates:

LOAD

    ID,

    My_Date as Without_Dual,

dual(My_Date ,-1 * Floor(MonthEnd(MakeDate(Num(Left(My_Date , 4)),Num(SubField(My_Date , '/', 2)),

1

        )))

    ) As My_Date_Dual, 

    DUAL(My_Date_year ,-1*My_Date_year ) As My_Date_Year_DUAL,

RESIDENT Source_Table;

// Find the max date in your data (month end)

MaxDate:

LOAD

    Max(MonthEnd(MakeDate(

        Num(Left(My_Date , 4)),

        Num(SubField(My_Date_Dual, '/', 2)),

        1

    ))) as MaxMonthEnd

RESIDENT Sort_Dates;

// Store max date in a variable

LET vMaxMonthEnd = Peek('MaxMonthEnd', 0, 'MaxDate');

// Drop the temporary MaxDate table

DROP TABLE MaxDate;

// Reload Data with Rolling 12 Months flag

DataWithRolling12Months:

LOAD

    *,

    // Calculate MonthEnd date for comparison

    MonthEnd(

        MakeDate(

            Num(Left(My_Date_Dual, 4)),

            Num(SubField(My_Date_Dual, '/', 2)),

            1

        )

    ) as MonthEndDate,

    // Flag if MonthEndDate is within last 12 months from max date

    If(

        MonthEnd(

            MakeDate(

                Num(Left(My_Date_Dual, 4)),

                Num(SubField(My_Date_Dual, '/', 2)),

                1

            )

        ) >= MakeDate(Year('$(vMaxMonthEnd)'), 1, 1) and

        MonthEnd(

            MakeDate(

                Num(Left(My_Date_Dual, 4)),

                Num(SubField(My_Date_Dual, '/', 2)),

                1

            )

        ) <= '$(vMaxMonthEnd)',

        'Yes',

        'No'

    ) as [YTD Inception],

    If(

        MonthEnd(

            MakeDate(

                Num(Left(My_Date_Dual, 4)),

                Num(SubField(My_Date_Dual, '/', 2)),

                1

            )

        ) >= AddMonths('$(vMaxMonthEnd)', -11) and

        MonthEnd(

            MakeDate(

                Num(Left(My_Date_Dual, 4)),

                Num(SubField(My_Date_Dual, '/', 2)),

                1

            )

        ) <= '$(vMaxMonthEnd)',

        'Yes',

        'No'

    ) as [R12 Inception]

RESIDENT Sort_Dates;

// Drop the original Data table if no longer needed

DROP TABLE Sort_Dates;