topic Re: Convert Selected date into number in Qlik Automate

Convert Selected date into number

Harish_Qlik — Thu, 06 Mar 2025 11:26:57 GMT

Hi,

is there any way to convert selected date into numeric? i want to convert 'GetFieldSelected(date,';')' into a number format?

i tried 'num(GetFieldSelected(Activitymonth,';'))' but its not working.

Re: Convert Selected date into number

PrashantSangle — Thu, 06 Mar 2025 11:29:50 GMT

just

num(date)? should work for you.

Didn't understood your question. Can you elaborate little bit more

Re: Convert Selected date into number

Harish_Qlik — Thu, 06 Mar 2025 11:33:31 GMT

i am trying to convert selected date in a sheet into a number, i can get selected date by using getselectedfield(date,',') now i want to convert into number.

Re: Convert Selected date into number

Or — Thu, 06 Mar 2025 11:36:56 GMT

Why are you using GetSelectedField()?

Just Num(DateFieldName), assuming one has been selected this should work. If it doesn't, your "date" is probably a string rather than a date and needs to be converted using Date#().

Re: Convert Selected date into number

Harish_Qlik — Thu, 06 Mar 2025 16:37:36 GMT

Hi,

i would like to store my current selected date values in a variable in numerical format (single value or multiple) so i can use that variable in Sheet action, currently i am using Getfieldselections(monthname), but the return i am getting is String value.

Re: Convert Selected date into number

henrikalmen — Thu, 06 Mar 2025 17:43:39 GMT

If your date value is actually a string and not an actual date-formatted date, you can use date#(). If your date is e.g. '2024-12-31' you can do this: num(date#('2024-12-31', 'YYYY-MM-DD'))

But that's kind of a sidenote. It seems that you are saying that you can have multiple selected different dates. In this case, getfieldselections(monthname) will result in a string like for example ''2024-12-31,2025-03-07' and you can't convert that into a number. I suppose that you want to convert it into a string with multiple numbers, something like '4711,4712'.

Maybe you could use concat(distinct num(monthname),',') instead of getfieldselections. Because just like @Or said, it sounds like you don't actually need GetFieldSelections() in this case.

Re: Convert Selected date into number

Harish_Qlik — Fri, 07 Mar 2025 16:54:37 GMT

Hi Henri

Let me elaborate my requirements,

i have multiple sheets, which have same type of fields and data only the group name is different, i have separated the sheets based on groups, when i select activity month (date field) and when i go to next group sheet i want to see same selection but for different group, even if i select multiple dates i should see multiple values selected in next sheet, also i when i land on any page by default it should select my reporting month which is previous month of current date. i am able to achieved all of this by storing my current selections in a variable, but only issue is when i select multiple dates and go to next sheet the selection automatically removed,

i hope i am able to explain my requirements.

Re: Convert Selected date into number

henrikalmen — Fri, 07 Mar 2025 21:47:45 GMT

Are you using actions that are triggered on sheet opening, that modified the selections? (If you aren't, selections should not change just because you change sheet, uless sheets are in alternate states.) How are those actions defined?

I don't understand why you are storing your current selections in a variable - what is the purpose of that?

Re: Convert Selected date into number

Harish_Qlik — Sun, 09 Mar 2025 21:17:30 GMT

Hi,

yes, i am using Sheet action for my all sheets, because when i open any sheet i want by default reporting date should be selected (like we are in March, my reporting month should be selected February) by default, similarly if i made changes in my date selection (like if i selected January or December and January both month) and if i move to different sheet my new selections should apply,

everything is working what i am expecting by storing selection values in a variable (GetfieldSelections(Fieldname,';') and in Sheet action i selected 'select value in a field' and in value field i mentioned the variable name, however my challenges are when i select multiple dates, that's where my sheet action does not work,

if i add multiple dates manually in numeric datatype in the variable it's working but when i use getfieldselections it doesn't work, i understand when i use getfieldselections it stores the values in string datatype.

Re: Convert Selected date into number

henrikalmen — Mon, 10 Mar 2025 09:03:01 GMT

Normally, without any sheet actions, selections stay with the user even when changing sheets. You can make selections on one sheet, go to another sheet and the same selections will still be there.

GetFieldSelections holds the current selections, and therefore your variable will change immediately when selections change. If your sheet action changes your selections, the variable will change accordingly.

It sounds like you need to take another approach. You could for example use if-statements to always use previous month in your calculations, unless there are selections made in the date fields. Or you could use a dynamic default bookmark that makes sure that the app opens with previous month selected, but after that it's up to what the users selects.

Re: Convert Selected date into number

Harish_Qlik — Mon, 10 Mar 2025 14:53:46 GMT

Hi,

yes i am using current selection in a variable by using if(GetSelectedCount(ReportingMonth)=0,MonthName(AddMonths(Today(),-1)),GetFieldSelections(ReportingMonth,';')), and in sheet action i am reffering to this variable,

could you please advice how make dynamic default bookmark that makes sure that the app opens with previous month selected, but after that it's up to what the users selects??

Re: Convert Selected date into number

henrikalmen — Mon, 10 Mar 2025 15:09:54 GMT

Ok, but GetFieldSelections(ReportingMonth,';') will give you a semi-colon separated string.How do you use the variable to make it into a new selection? You say you are "reffering to the variable", but how?

See this support video on how to create dynamic default bookmark: https://help.qlik.com/en-US/video/tFyqOigqnUw

Re: Convert Selected date into number

lennart_mo — Mon, 10 Mar 2025 16:23:25 GMT

Hi @Harish_Qlik,

do you set your variable by evaluating this equation?

If not, do so:

'$(=if(GetSelectedCount(ReportingMonth)=0,MonthName(AddMonths(Today(),-1)),GetFieldSelections(ReportingMonth,';')))'

And afterwards put your variable in the 'Select Values in a field'-action like so:

='$(yourVariable)'

Evaluating your equation is important, because that way the string is set before you delete the user-made selections. The syntax in the second step keeps the value of the variable as a string, needed to make the right selections.

Let me know if this worked for you!

Re: Convert Selected date into number

Harish_Qlik — Mon, 10 Mar 2025 23:39:30 GMT

Hello Lennart

the above solution is working only when i select 'Select values matching search criteria' but no in 'Select values from a field' because the above syntax only return string value, and 'Select values from a field' is only accept numeric values, and the sheet action is working only when user select one date value, if multiple date values being selected then the sheet action doesn't work and selection for a sheet automatically disappeared.

Re: Convert Selected date into number

Harish_Qlik — Tue, 11 Mar 2025 16:33:59 GMT

Hi all,

Thank you for all your support, now finally i am able get what i was looking for..

here is the solution I have applied:-

First, i created a variable with the expression =if(GetSelectedCount(Reportingmonth)=0,num(MonthName(AddMonths(Today(),-1))),Concat(distinct(num(Reportingmonth)),';'))

and in the sheet action, i choose in the action 'Select values in a field', and in the field section i choose reporting month field, and in the values expression i mentioned the variable name.

the variable will return the default value(which is previous month) upon landing, and whatever i change the selection in the sheet it is carried forward to next sheet.

thanks ones again.