3 Replies Latest reply: May 22, 2013 9:10 AM by Mika Lindman

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

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?

• ###### Re: Problem with Count( If ( validFrom < CalDate and validTo > CalDate ), CustomerId )

Hi mlindman ,

Try this

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

I hope it works

• ###### Re: Problem with Count( If ( validFrom < CalDate and validTo > CalDate ), CustomerId )

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.

• ###### Re: Problem with Count( If ( validFrom < CalDate and validTo > CalDate ), CustomerId )

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.