Using Set Analysis Dates along with Another Argument

[#1]

I have created a pivot table that looks like the following:

For the expression for quotes I have

Count({$<QvsI = {"Q"}, OriginalDate={"=CalendarDateKey"}>}QvsI).

What I need is for Quotes to be counting the number of records where QvsI = Q and OriginalDate = CalendarDateKey. The first one meets this criteria. Why would it not be counting it but it is counting PolicyNumberC=5018237? I think I may need to be using the date() function somewhere in the set analysis so that the dates will compare correctly but I'm not sure.

[kaushiknsolanki]

Hi

   Try this

   Count({$<QvsI = {"Q"}, OriginalDate=p(CalendarDateKey)>}QvsI)

Regards,

Kaushik Solanki

[#3]

When I change the expression to the above it makes all of the Quotes column zero:

[#4]

Kaushik's response worked for me. Is it possible your dates have un-equal timestamps?

[#5]

There is no time stamp in the data that is being pulled into the load but I am converting the fields to date format in the load. See the attached test .qvw.

[#6]

Try adding this as a calculated dimension:

=

date(Responses.ResponseDate, 'MM-dd-yyyy hh:mm:ss')

It looks like your ResponseDate data does have timestamps in it. Formatting the date coming into QV does not eliminate the timestamp, it just obfuscates it.

[#7]

Ohh. Is there a function that will completely take that off of the ResponseDate so that it will compare correctly?

[#8]

if(text(OriginalDate)=text(CalendarDateKey), Count({$}QvsI), '0')

[#9 · Accepted solution]

Use the floor function on the integer values for the dates.

like this: date(floor(ResponseDate),'M/D/YYYY') as CalendarDateKey. This will set the CalendarDateKey value to be at midnight of the current day: 8/4/2011 8:16:36 to 8/4/2011 00:00:00.

[#10]

That seems to work. Thank you for all of your timely responses!!