Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

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

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.

6 Replies
alextomlins
New Contributor II

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

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

alextomlins
New Contributor II

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

Nope hahah

aunahsan
New Contributor III

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

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

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

mark6505
Valued Contributor III

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

Hi,

Not sure i fully follow, But maybe try something like

IF(SUM(ActiveCount)>0,

     1,

     0)

Mark

zebhashmi
Valued Contributor

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

Maybe

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

sasiparupudi1
Honored Contributor III

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

May be try

Sum(

IF(

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

  )

)