min date from data set

[maxsheva]

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.

[antoniotiman]

Hi Max,

You forgot $ sign.

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

[antoniotiman]

However, why not simply

Date(Min(date))

or

MinString(date)

?

[tresesco]

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.

[mayuresh_d]

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

[maxsheva]

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.

[sunny_talwar]

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

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

[maxsheva]

Hi Sunny,

You brought me the idea.

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

Thank you!

[swuehl · Accepted solution]

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)

or

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.

[swuehl]

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