Max and min date in datepicker - QlikSense and NPrintint Designer AddOn called Ondemand.

[mafy]

We are developing on QlikSense and NPrintint Designer and between them we put the AddOn called Ondemand.

The output reports are in excel or pdf format. 

We are using in  QlikSense the object Date picker to select range of dates to filter data in dashboard.

In order to get all the selected filters in the Qlik Sense dashboard, we use the function GetCurrentSelections which lists all filtered fields.

The problem is that we have in output in the report excel/pdf a list of dates like this:

DATA:2020-01-02 00:00:00.000000, 2020-01-03
00:00:00.000000, 2020-01-04 00:00:00.000000, 2020-01-05
00:00:00.000000, 2020-01-06 00:00:00.000000, 2020-01-07
00:00:00.000000, 2020-01-08 00:00:00.000000, 2020-01-09
00:00:00.000000, 2020-01-10 00:00:00.000000, 2020-01-11
00:00:00.000000, 2020-01-12 00:00:00.000000, 2020-01-13
"

We would like to have only the MAX and min date of the selection above

Thanks in advance for any suggestion

[Lech_Miszkiewicz · Accepted solution]

Hi, 

Is your aim to get min and max selected? If so why don't you use Min(Date) and Max(Date)  instead of GetCurrentSelections?

If the goal is to list also all other selections as well as min and max of date then you need to workaround with the output of GetCurrentSelections() function. This becomes then not a NPrinting question, but basic UI development question, right?

My understanding is that your date picker is actually selecting all date values between the selected min and max dates and those are the records which you want avoid showing in current selection

so, how can we go about it... I say we do following:

first establish delimiters for: records, tags and values. In my example they are "|" ,"#" and "," so using formula like:

=GetCurrentSelections( '|','#',',',10000) 

will split field names and values with # sign, will also split fields with | sign and individual values will be split with , (commas)

This is good start as from there we can extract what we need (being full date selections) and then extract from that first and last vale and once extracted replace the full range of dates with the first and last. 

Here is how i would do it (note that my date field name is: "Date"

Replace(
	Replace(	
		Replace( 
			GetCurrentSelections('|','#',',',10000),
				TextBetween( GetCurrentSelections('|','#',',',10000)&'|','Date#','|'),
				Subfield(TextBetween( GetCurrentSelections('|','#',',',10000)&'|','Date#','|'),',',1)&
				' - '&
				Subfield(TextBetween( GetCurrentSelections('|','#',',',10000)&'|','Date#','|'),',',-1))
		,'#',': ')
	,'|',' ') 

Note that I am using replace function to later replace "#" and "|" with some user friendly characters.

In screenshot below I used QlikView (same applies to Qlik Sense) and you can see the results. i highlighted in different colours individual steps to show you which portion does what. The big green highlight is the final result which you would normally use. Is that what you expect to see?

[Lech_Miszkiewicz]

update: on screenshot I have "1" instead of  "-1" in subfield statement. -1 is used to get the last value. hence on screenshot you have the same values in final result. 

code snippet i provided is correct though so it will work 

[mafy]

Thank you, it works like you suggest

[Lech_Miszkiewicz]

great - just one more comment, and it is important!!!

If you select dates, say: whole May 2021 and also another field (for example country: Australia, city: Sydney) which possibly can change available values in date fields based on associations then only possible values will be shown in your report.

This is the result of how OnDemand passes selections as filters to QlikSense as it only passes intersection of all applied filters!!!

The only scenario when this will not happen is when your date field is not linked to other fields and that has been also discussed on community, so if you are interested to dig more i suggest to go through this discussion: https://community.qlik.com/t5/Qlik-NPrinting-Discussions/display-all-selections-with-getfieldselecti...