QS | Text value of dates is not getting link to dual field of date

[Karim_Khan]

Hi,

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

The problem statement is when clicking on R12 button my new Date1 which got created using Dual is not getting selected with green color although its getting associated as per Qlik associative nature.

I tried many approache to get this link but no luck.

[Karim_Khan · Accepted solution]

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;

Thanks @marcus_sommer @Or  for your initial guidance to start with this approach.