topic Re: min date from data set in QlikView

min date from data set

maxsheva — Sun, 20 Aug 2017 21:38:45 GMT

Hi,

data:

LOAD *, Year(date) as year;

LOAD * Inline [

date, product, value

01.01.2016, product1, 100

01.01.2017, product1, 100

01.02.2017, product1, 100

01.03.2017, product1, 100

01.04.2017, product1, 100

];

I need to get minimum date from the data above. In set analysis I use this =Date(min({<date={'>=(=min(date))<=$(=max(date))'}>} date))

When I select from list box some date, for example "01.02.2017" the result is "01.01.2016"

When I select "2017" year the result is "01.02.2017". But I still need the min in data set.

Any idea would be appropriate.

Thx.

Re: min date from data set

antoniotiman — Sun, 20 Aug 2017 22:08:38 GMT

Hi Max,

You forgot $ sign.

=Date(min({<date={'>=$(=min(date))<=$(=max(date))'}>} date))

Re: min date from data set

antoniotiman — Sun, 20 Aug 2017 22:09:47 GMT

However, why not simply

Date(Min(date))

MinString(date)

Re: min date from data set

tresB — Mon, 21 Aug 2017 05:01:32 GMT

Right syntax is:

=Date(min({<date={'>=$(=min(date))<=$(=max(date))'}>} date))

and if you want the out put to be always irrespective of selection, the syntax would be like:

=Date(min({1}{<date={'>=$(=min({1}date))<=$(=max({1}date))'}>} date))

Now, if you only need the min/max date of data set all the time, a simpler expression could be:

=Date(min({1} date))

Note: '{1}' is used in set analysis to defy any selections.

Re: min date from data set

mayuresh_d — Mon, 21 Aug 2017 10:24:28 GMT

use following set exp:

=date(num#(min({<date=>}num(date(date#(date,'DD.MM.YYYY'),'YYYYDDMM')))),'DD.MM.YYYY')

it will give u min date...if you select any date it will not change. but on selection of product or value it will change....if you dont want it to change at all for that just nullify this two fields like what have done in set.

thanks

Re: min date from data set

maxsheva — Mon, 21 Aug 2017 19:51:05 GMT

Thanks everyone for reply.

Probably I didn't quite explain what I exactly need.

Attached file in my first post has expression Sum( {<date={'>=(=min(date))<=$(=max(date))'}>} value)

In this case I need sum all values started from first date in data set.

No need add dollar sign to minimum date in set analysis. I need ignore date filter for minimum value.

My goal is to get minimum date "01.01.2016" in set analysis when selected any date as well as any year in filters.

Re: min date from data set

sunny_talwar — Mon, 21 Aug 2017 19:59:36 GMT

May be this to get min date regardless of selection in date or year

=Date(min({<date, year>} date))

Re: min date from data set

maxsheva — Mon, 21 Aug 2017 20:21:12 GMT

Hi Sunny,

You brought me the idea.

will use this for my expression Sum( {<year,date={'>=(=min(date))<=$(=max(date))'}>} value)

Thank you!

Re: min date from data set

swuehl — Mon, 21 Aug 2017 20:25:02 GMT

If you want to start from first date in the total data set, but end with selected date:

Sum( {<date={">=$(=min({1} date))<=$(=max(date))"}, year >} value)

Sum( {<date={"<=$(=max(date))"}, year >} value)

You need to add all calendar fields that may interfere with the date modifier same way like year field to clear possible user selections in these fields.

Re: min date from data set

swuehl — Mon, 21 Aug 2017 20:25:51 GMT

And don't forget the dollar sign for the dollar sign expansion for both date limits.

Re: min date from data set

maxsheva — Mon, 21 Aug 2017 20:54:21 GMT

would you please clarify the difference between

yours Sum( {<date={">=$(=min({1} date))<=$(=max(date))"}, year >} value)

and this variant Sum( {<date={'>=(=min(date))<=$(=max(date))'}, year>} value)

Are these the same versions?

Re: min date from data set

swuehl — Mon, 21 Aug 2017 21:02:59 GMT

Well, second expression shows incorrect syntax, but Qlik is gracious and I think it will be interpreted like the version missing the lower limit:

Sum( {<date={"<=$(=max(date))"}, year >} value)

Re: min date from data set

maxsheva — Mon, 21 Aug 2017 21:09:13 GMT

Thank you for the explanation.

Re: min date from data set

ClickNorth — Mon, 17 Jun 2019 16:37:38 GMT

Hi,

I have a slightly more specific problem.

I want to sum for the max value date and min start of fiscal year date.

Where the fiscal year start is the 6th April.

So I have a FY to date field. I want it to sum from financial year start to max value date.

I've tried defining the start date, but I can't get it to work.