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.

[Or]

If memory serves, the association always happens based on the numeric value in a dual(). In your scenario, you seem to have one textual value and one numeric value. As I mentioned in your previous thread, you're going to have issues with your dates if you swap out the numeric values for something else... 

[Karim_Khan]

Well in that case Dual() will not serve purpose to link with existing dates.If my sorting logic working then date linking issue is arising.

Will there be any alternative solution to this or we can pass this as qlik limitation?

[Or]

You can have one field the users select on, and another field used as a key, e.g.

Load Dual(Date, -Date) as SelectionField, Date as Key From DateTable;

Load Date as Key, Field1, Field2 from SomeTable; 

[marcus_sommer]

I wouldn't do such stuff within the UI else within the data-model on the calendar-level. It needs not much efforts to add n YearMonth fields, for example:

date(monthstart(MyDate), 'MyPattern') as YearMonth_X

which are further real dates with any formatting as well as useful information like:

year(MyDate) * 100 + month(MyDate) as YYYYMM100
year(MyDate) * 12 + month(MyDate) as YYYYMM12

which could be then used to calculate offsets and flagging them, for example:

(year(today()) * 12 + month(today())) - YYYYMM12 as YYYYMM_Offset
year(today()) - year(MyDate) as YearOffset

and of course they might be also set to negative and further then things like:

if(YYYYMM_Offset < 12, 1, 0) as Rolling12

and/or stuff like:

sign(daynumberofyear(today()) - daynumberofyear(MyDate)) as YTD_Base

and so on ... and all of these flags might be then also added to The As-Of Table - Qlik Community - 1466130.

There are only limitations if the essential work is attempted within the UI instead of in the data-model.

[Karim_Khan]

@marcus_sommer Well the proposal you have mentioned it will work with the dates when we are using text data type in data model.In my case i am trying to get the Rolling 12 months action button for Numeric data type that was created using Dual and it's not working with.

Hence i was trying to incorporate something like @Or  mentioned but still not sure how it will be achieved.

Load Dual(Date, -Date) as SelectionField, Date as Key From DateTable;

Load Date as Key, Field1, Field2 from SomeTable; 

[marcus_sommer]

In general should the selections be working with the most kind of YearMonth values und the shown:

date(monthstart(MyDate), 'MyPattern') as YearMonth_X

is a dual() respectively each real date is a dual() because it has a string-interpretation and a pure numeric value.

Beside this - you may not need such complex search-selections else you could create all needed periods within the data-model which means R12, YTD, ... could be directly selected.

[Karim_Khan]

I do agree that below expression will give desired kind of format of YearMonth and data is also getting The problem statement is when clicking/Selecting Rolling12 field new Date1 which got created using Dual is not getting selected with green color although its getting associated as per Qlik associative nature.

date(monthstart(MyDate), 'YYYY/MM') as YearMonth

[marcus_sommer]

Make sure that your selections and also the shown panel goes against native fields and field-values. This means the selection shouldn't be a search-string and the shown panel shouldn't be something like: date(MyDateField) or if(condition, MyDateField) or something similar. Always only pure fields and pure field-values. 

[Karim_Khan]

I tried it and many other iterations also but it's not working.I am not sure if it can be achieved or not in Qliksense?