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

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

    Mika Lindman

      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?