ignore all fields - confusion`

[#1]

Hi

I am using this expression (in a text box for testing purposes)

=date(min({$< $(='['&Concat({1<$Field-={'Date'}>}distinct $Field,']=,[')&']=')>} Date))

This (to my knowledge) should be excluding EVERY other field in the entire model with the exception of itself.

When I select the Year 2016 I am expect to see 01/01/2016 as the result.  This works.

What I can't figure out is; Why, when I select a product category (for example) does the date jump to 04/01/2016 (i.e. the first invoice date) when the expression should be ignoring every other field...?

Any help would be appreciated.

Thanks

Oli

[sunny_talwar · Accepted solution]

Why don't you try this:

=Date(Min({1<Date = $::Date>} Date))

[sunny_talwar]

The expression will change only and only based on selection in Date field..... If you need to add Year, you can do this

=Date(Min({1<Date = $::Date, Year = $::Year>} Date))

[#4]

Hi Sunny

I have never seen the $:: syntax.  Would you mind explaining and/or point me in the direction of further reading on it?

Thanks

Oli

[sunny_talwar]

You can check here:

Set Analysis: syntaxes, examples

[#6]

This still acts in the same way but is a lot shorter.  I think its due to the selection field being in a different table to the calendar, so although it is ignoring all other fields the first valid date in the calendar table that links to the field I selected is the 04/01/2016.

Oli

[sunny_talwar]

Where are you using this expression? I hope not in a list box object, are you?

[#8]

No; Text boxes.

I have a landing page with some key business sectors.  I need to 'lock' the values to the category but still have the other selections adjust the figure down, for example by sales person.

I have made it so that the user will select a date period and the model will auto compare against the same period last year. Something wasn't quite right so while debugging I wanted to check my facts on this behaviour.

[sunny_talwar]

I don't know why, but the below expression should not change based on any selections except in date field. If you can share a sample, I might be able to look at it, but without a sample, I don't know what might be the issue.

=Date(Min({1<Date = $::Date>} Date))

[#10]

In trying to create a sample file for you I think I have realised where I was going wrong to cause this behaviour.

Thanks again for your help.

Oli