topic Re: Using Min function in Set Analysis expression in QlikView

Using Min function in Set Analysis expression

lobmeister165 — Mon, 23 Jun 2014 08:02:46 GMT

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

Re: Using Min function in Set Analysis expression

tresB — Mon, 23 Jun 2014 08:15:44 GMT

Try:

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

Re: Using Min function in Set Analysis expression

lobmeister165 — Mon, 23 Jun 2014 08:27:26 GMT

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

Re: Using Min function in Set Analysis expression

tresB — Mon, 23 Jun 2014 08:40:24 GMT

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?

Re: Using Min function in Set Analysis expression

lobmeister165 — Mon, 23 Jun 2014 08:50:17 GMT

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

Re: Re: Using Min function in Set Analysis expression

tresB — Mon, 23 Jun 2014 09:12:51 GMT

May be this?

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

Re: Using Min function in Set Analysis expression

lobmeister165 — Mon, 23 Jun 2014 09:32:07 GMT

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])