Chart count expression

[#1]

Hi,

I have the following data:

CustomerID	ContractID	StartDate	EndDate
16	135	02.05.2010 00:00:00	NULL
17	654	12.12.2000 00:00:00	01.01.2012 00:00:00
18	317	31.01.2007 00:00:00	NULL
19	890	10.06.2002 00:00:00	NULL

I've set up a gauge chart with the following expression: Count ({1} DISTINCT CustomerID)

The chart displays the total count of CustomerID regardless of selection, but i need to filter some more.

A CustomerID can have multiple ContractIDs and also I need to verify that the EndDate is after todays date or null.

I'm having a hard time figuring out the correct syntax to use in the Edit Expression window. A little help would be nice

[Anonymous]

Hi,

Syntax should be something along the lines of:

=count({1<EndDate={'>$(=Now(0))'}>} Distinct CustomerID)

However, it would be nicer to strip the time part of the dates if you are only concerned with the day and use the Today() function to match your EndDates to.

You might also want to create a CustomerFlag or something if you have a separate table with your customers with the value 1 for each unique customer. This way you can use Sum(CustomerFlag) which is a lot more responsive than using Count(Distinct CustomerID)

[#3]

Thanks for replying!

Just to make sure we're on the same page: CustomerID appears several times as some customers have several contracts. Different customers in some case have the same ContractID as their one contract or as one of their many contracts.

I might be missing the point, but I don't see how a CustomerFlag would help as I need to count every active contract. Contracts are considered active if their startdate is today or in the past and the enddate is in the future.

[jonathandienst · Accepted solution]

Hi

You would set the CustomerFlag during the load script to 1 for each contract without an end date and for each contract with end dates > today.

Something like:

     Contracts:

     LOAD ....

          ....

          If(IsNull(EndDate) Or EndDate > Today(1), 1, 0) As CustomerFlag

          ...

     FROM ...

Then (after a reload of course!) in your expression

     Count({<CustomerFlag = {1}>} Distinct CustomerID)

Hope that helps

Jonathan

[#5]

Thanks! That's what I needed