Using Min function in Set Analysis expression

[lobmeister165]

Hi,

I am trying to use indirect set analysis expressions to filter a count of records based on a minimum value and keep getting incorrect values.

More specifically, I need to count a number of customer who have a 'service start date' within the time period currently selected BUT ONLY where that start date is the first (Minimum) Start Date for each particular customer. Basically I need a count of customers where the service is their first to date within the currently selected time period.

So far I have tried the following expression :

using comma separation of expressions:

=Count({$<[Service Start Date]={">=$(varMinDate)<=$(varMaxDate)"}, [CustID]=E({1<[Service Start Date]={"<$(varMinDate)"}>})>} [CustID])

..and the * operator for intersection:

=Count({$<[Service Start Date]={">=$(varMinDate)<=$(varMaxDate)"}> * $<[CustID]=E({1<[Service Start Date]={"<$(varMinDate)"}>})>} [CustID])

in this expression the variables I use evaluate the minimum (varMinDate) and maximum (varMaxDate) dates within the date range currently selected by the user.

Please help, I have spent a lot of work time and internet research time trying to get this right without success.

Many Thanks

Elliot Kaye

[tresesco]

Try:

=Count({$<[Service Start Date]={">=$(varMinDate)<=$(varMaxDate)"}, [CustID]=E({1<[Service Start Date]={"<$(varMinDate)"}>}[CustID])>} [CustID])

[lobmeister165]

Hi tresesco,

I tried that and although it did further filter my count of records meeting the criteria, upon a manual inspection of all customer records and their minimum start date within the data model, I find that there are more than is being returned by the above expression.

So for example I choose the currnet financial year as time period and the two variables will evaluate to varMinDate = 01/04/2014 and  varMaxDate = 31/03/2015.

What I need out of all the customers contributing to service expenditure in this finacial year is a count of only those with their first service start date in this financial year

Thanks again,

Elliot

[tresesco]

Elliot Kaye wrote:

of only those with their first service start date in this financial year

So, a customer would have multiple service start date, right? If so, do you have any flag to determine the first one?

[lobmeister165]

Yes - a customer may have one or more service start dates.

There is no 'flag' in the data. Services are in one table and Customers in another, they are joined via a 'link table' via their respective ID fields - Service ID and Cust ID

If I add a striaght table and use CustId as dimension and Min(Service Start Date) as expression, I get the correct, unique first date for each customer. This is what I am checking the count against

[tresesco]

May be this?

=Count({$<[Service Start Date]={">=$(varMinDate)<=$(varMaxDate)"}, [CustID]=P({$<[Service Start Date]={"=$(varMinDate)"}>}[CustID])>} [CustID])

[lobmeister165 · Accepted solution]

Hi Again

Not quite - but it led me to the correct expression so thanks for your help.

This one does it :

=Count({$<[Service Start Date]={">=$(varMinDate)<=$(varMaxDate)"},
[Client ID]-=P({$<[Service Start Date]={"<$(varMinDate)"}>} [CustID])
>}
[CustID])