2 views
in BI

I have a function that categorizes calls a user has made into 3 categories using this calculation:

IF 0 <= DATEDIFF('dayofyear', [SubmittedDateTime], [CALLDATE])

AND DATEDIFF('dayofyear', [SubmittedDateTime], [CALLDATE]) <= 7

THEN "Week After"

ELSEIF -7 <= DATEDIFF('dayofyear', [SubmittedDateTime], [CALLDATE])

AND DATEDIFF('dayofyear', [SubmittedDateTime], [CALLDATE]) < 0

THEN "Week Before"

ELSE "Not within a week"

END

I was wondering if it's possible to count the number of occurrences of a particular outcome of the function on a per-user basis in order to then categorize each user based on the number of occurrences. I'm attempting to use this calculation to do so:

IF { FIXED [SUBID]: COUNT([DateDiff Calc] = 'Week After')} = 1

THEN "1 Conference User"

ELSEIF { FIXED [SUBID]: COUNT([DateDiff Calc] = 'Week After') } > 1

THEN "Multiple Conference User"

ELSE "0 Conference User"

END

but the COUNT function I'm using is not working properly it seems. It seems that the COUNT function is also counting occurrences of both "Week Before" and "Not within a week" from the first function.

by (47.2k points)
• Problem exists in the measure part of your LOD expression :

COUNT([DateDiff Calc] = 'Week After')

• It results in giving the count of both times: when your conditions are met and when it's not met. Either true or false will be returned by [DateDiff Calc] = 'Week After', both will be counted as +1 in the count function.

• You could try something like:

IF { FIXED [SUBID]: SUM(IF[DateDiff Calc] = 'Week After' THEN 1 ELSE 0 END)} = 1 THEN "1 Conference User" ...