6 Replies Latest reply: Apr 27, 2018 3:27 PM by Sasidhar Parupudi RSS

    Expression help - sum if user has a 1 in any week row for the month.

    Alexander James Tomlins

      Hi guys,

       

      Sorry for another question! I promise i am getting better

       

      So we have a table which is created using interval match but it details a row for every week a user was active. So if a user was active for two weeks - it would have two rows - one for each week of activity. This is all linked via a master calendar split by date. Then to get an idea of total active users per week we just sum the ActiveCount and use a week dimension.

       

      The problem is when i come to look at this at months. Because the user has an active count per week it creates dupes -

       

      What i need it an expression to only count that user if they have an active count at all in any of the weeks associated with that month.

       

      So it's SUM(ActiveCount) and then i use YearWeek as a dimension and because the table is weekly that comes out OK.

       

      But if i count Sum(ActiveCount) with YearMonth if the user is active for two weeks it comes out as a count of 2 - which is wrong, because they were only active once in that month.

       

      again - What i need it an expression to only count and active count for that month if the user has an active count in any of the weeks in that month.

       

      I just really need the approach - i might be able to figure out the syntax.