topic Re: Chart count expression in QlikView

Chart count expression

Mon, 15 Oct 2012 09:34:11 GMT

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

Re: Chart count expression

Anonymous — Mon, 15 Oct 2012 10:11:55 GMT

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)

Re: Chart count expression

Mon, 15 Oct 2012 11:32:35 GMT

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.

Re: Chart count expression

jonathandienst — Mon, 15 Oct 2012 11:51:07 GMT

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

Re: Chart count expression

Mon, 15 Oct 2012 12:30:14 GMT

Thanks! That's what I needed