topic Re: Min & Max Dates GetFieldSelections for report in QlikView

Min & Max Dates GetFieldSelections for report

Anonymous — Wed, 15 Mar 2017 12:23:44 GMT

Hi all

I want to show the dates selected in a report but when I put the selections in the report it show 11 of 45 for months.

I have managed to create an expression which I can add to a text box and show the selections below :

=GetFieldSelections(Month, '; ', 100) & ' | ' & GetFieldSelections(Division)

This shows all dates selected. and shows the Different Divisions

Apr 2015; May 2015; Jun 2015; Jul 2015; Aug 2015; Sep 2015; Oct 2015; Nov 2015; Dec 2015; Jan 2016; Feb 2016; Mar 2016; Apr 2016; May 2016; Jun 2016; Jul 2016; Aug 2016; Sep 2016; Oct 2016; Nov 2016; Dec 2016; Jan 2017; Feb 2017; Mar 2017; Apr 2017; May 2017; Jun 2017; Jul 2017; Aug 2017; Oct 2017 | North Division

Is there a way I can adapt this so that it only shows Min date & max date so that I can show the following

Dates : Apr 15 to Oct 17

North Division

Many thanks

Phil

Re: Min & Max Dates GetFieldSelections for report

adamdavi3s — Wed, 15 Mar 2017 12:30:26 GMT

=min(Month)&' to '&max(Month)& ' | ' & GetFieldSelections(Division)

*edited* This might potentially work but you'll probably have to convert your month field to a numeric to make it work

Re: Min & Max Dates GetFieldSelections for report

sunny_talwar — Wed, 15 Mar 2017 12:34:42 GMT

If it is not, then may be this

=Date(Min(Date#(Month, 'MMM YYYY')), 'MMM YY') & ' to ' & Date(Max(Date#(Month, 'MMM YYYY')), 'MMM YY') & ' | ' & GetFieldSelections(Division)

Re: Min & Max Dates GetFieldSelections for report

adamdavi3s — Wed, 15 Mar 2017 12:35:08 GMT

Yeah something like this

=Date(min(num(date#(MonthYear, 'MMM YYYY'))),'MMM YYYY')&' to '&Date(max(num(date#(MonthYear, 'MMM YYYY'))),'MMM YYYY')& ' | ' & GetFieldSelections(Division)

Re: Min & Max Dates GetFieldSelections for report

adamdavi3s — Wed, 15 Mar 2017 12:35:48 GMT

Damnit stalwar1‌ you beat me by literal seconds

Re: Min & Max Dates GetFieldSelections for report

sunny_talwar — Wed, 15 Mar 2017 12:36:37 GMT

I think that extra second to add Num was the culprit

Re: Min & Max Dates GetFieldSelections for report

Anonymous — Wed, 15 Mar 2017 12:38:36 GMT

That's it. Works great.

One Last thing. Is there any way of adding a carriage Return so the different selections are on different rows. If not that's fine

Cheers

Phil

Re: Min & Max Dates GetFieldSelections for report

adamdavi3s — Wed, 15 Mar 2017 12:39:26 GMT

yep use chr(13)

=Date(Min(Date#(Month, 'MMM YYYY')), 'MMM YY') & ' to ' & Date(Max(Date#(Month, 'MMM YYYY')), 'MMM YY') & chr(13) & GetFieldSelections(Division)

Re: Min & Max Dates GetFieldSelections for report

sunny_talwar — Wed, 15 Mar 2017 12:40:23 GMT

Or Chr(10)

Re: Min & Max Dates GetFieldSelections for report

adamdavi3s — Wed, 15 Mar 2017 12:41:44 GMT

if you want a LF rather than a CR 😉

Re: Min & Max Dates GetFieldSelections for report

Anonymous — Wed, 15 Mar 2017 12:55:38 GMT

All great. Thanks All