Count Instances with two conditions

building reports desktopintelligence
1

Hi,

I have a report where I need to do a count of instances by day and by user, I can do the formula in excel:

COUNTIFS([DATE_RANGE],[@[DATE], [USER_RANGE],[@[USER],[TARGET_RANGE],[@[TARGET])
Where [TARGET] is the item to be counted, example attached

This is what I done so far in a variable, but when I add Date returns #ERROR

COUNT([TARGET]) IN ([USER]) returns the number of unique TARGET in all the rows

COUNT([TARGET]) IN ([USER];[DATE]) returns #ERROR

as an Add On when I check the filed type for [DATE] seems to be a string as; IsString([DATE]) = 1 and all the others (IsDate; IsNumber; IsLogical; IsTime) = 0

so I am a bit confused… :?

Any help and all the help is very much appreciated,

vitordf59 (BOB member since 2015-08-28)

2

If you have access to the universe (or can get someone to do it for you), change the qualification of the date object to date.

Otherwise, you have to create a report-level variable from the object using the ToDate() function.

The other thing it’s not a bad idea to do (especially if you are going to be doing further processing in Excel) is to go through your report column by column and make sure the columns are formatted correctly (it seems to help when the Excel output is created). Something that looks right on the screen may not be formatted correctly.

Maddye :uk: (BOB member since 2009-01-09)