topic Using Set Analysis Dates along with Another Argument in QlikView

Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 18:56:45 GMT

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.

Using Set Analysis Dates along with Another Argument

kaushiknsolanki — Tue, 01 May 2012 18:59:05 GMT

Try this

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

Regards,

Kaushik Solanki

Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 19:06:16 GMT

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

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 19:23:06 GMT

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

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:14:12 GMT

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.

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:31:27 GMT

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.

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:44:40 GMT

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

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:46:08 GMT

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

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:49:22 GMT

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.

Re: Using Set Analysis Dates along with Another Argument

Tue, 01 May 2012 20:57:09 GMT

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

Using Set Analysis Dates along with Another Argument

Sun, 13 May 2012 14:18:00 GMT

Hello Kaushik,

Earlier you helped me with an expression. Seeking your help once more

I'm creating a Historical Sales Trend using set analysis to select dates, until i had two historical dates it was ok, one bar was for the max date & the other for the Remaining one date. But now i've three snapshots.

The user wants to select 5 Snapshot dates in future. The probelm is if i use only list box for the date then the second ba wll show sum of all the remaining 4 dates.

i've also tried generating the 5 dates as:

SnapshotCalendarTemp1:
load Distinct
([Snapshot Date]) as [Snapshot Date1] resident Sales;

SnapshotCalendarTemp2:
load Distinct
([Snapshot Date]) as [Snapshot Date2] resident Sales;

SnapshotCalendarTemp3:
load Distinct
([Snapshot Date]) as [Snapshot Date3] resident Sales;

SnapshotCalendarTemp4:
load Distinct
([Snapshot Date]) as [Snapshot Date4] resident Sales;

SnapshotCalendarTemp5:
load Distinct
([Snapshot Date]) as [Snapshot Date5] resident Sales;

to create 5 separate list boxes & then using the expressions to create the list boxes depending on the selection in the previuos list box, hiding/showing them successively.

But its not working properly

PLz help

Regards

Using Set Analysis Dates along with Another Argument

kaushiknsolanki — Sun, 13 May 2012 15:22:31 GMT

Hi,

I request you to please create a new discussion on this and also provide the same example in QVW format and describe what you want and what you are trying to do.

This will help you to get faster solution as well as help others who have similar problem.

Regards,

Kaushik Solanki