Problem with Count( If ( validFrom < CalDate and validTo > CalDate ), CustomerId )

[#1]

My Customer records have validFrom and validTo values that I need to take in account when displaying customer amounts for time period.

When I set a single day as dimension and Count( If ( validFrom < CalDate and validTo > CalDate), CustomerId ), I get a graph that looks right.

But when I change my dimension to CalFirstDayOfMonth, the results are duplicated by 30, 31 or 28 (365 if I use year as dimension). I know I could use Count ( Distinct ... ), but this slows everything down too much.

My Calendar-table and Customer-tables are not linked, so why does that if statement produce a hit for each day, instead of just one hit given the dimension being rendered?

[#2]

Hi mlindman ,

           Try this

      =Count({<calDate={<validTo>validFrom}>}CustomerId)

I hope it works

[#3]

I changed your suggestion lightly:

=Count ( { <calDate = { "<validTo>validFrom" }>} CustomerId)

This no longer gives "count hits" for each day of the month, but it doesn't seem to actually check the statement for each month-dimension: I get value of 1 for each month, even though validFrom and validTo should limit it to just certain months.

[#4]

It seems that these suggestions don't actually run the comparison for each dimension value, just once for the whole chart. If I have validFrom 2001-01-01 , validTo 2002-01-01, it will still count for years 2003, 2004 etc.