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

[alextomlins]

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.

[alextomlins]

I think i may have solved it with Sum(IF([ActiveCount]>0,1,0)). Still looking to see if this has worked.

[alextomlins]

Nope hahah

[aunahsan]

You have the right idea here i think. it would have to be :

IF(Sum([ActiveCount]) > 0, 1, 0)

[Mark_Little]

Hi,

Not sure i fully follow, But maybe try something like

IF(SUM(ActiveCount)>0,

     1,

     0)

Mark

[zebhashmi]

Maybe

if ([ActiveCount]) > 0,Sum([ActiveCount])

[sasiparupudi1]

May be try

Sum(

IF(

Aggr(Max([ActiveCount]),Month)>0,1,0

  )

)