topic Two calendar using in one set analysis expression in QlikView

Two calendar using in one set analysis expression

Wed, 05 Jun 2013 10:53:26 GMT

Hi,

I'm trying to compare two dates from the different calendars and it works when I use IF clause, but doesn't with set analysis.

I want to calculate entries with Closing Date more or equal to Creation Date.

IF formula is

sum(if(Responses.ClosingDate>=Calendar_ResponseCreationDate.Datum,Responses.ResponsesCounter))

Set analysis I used:

sum({$<Responses.ClosingDate= {'>=$(Calendar_ResponseCreationDate.Datum)'} >}Responses.ResponsesCounter)

How can I change set analysis to get right figures?

Sokkorn — Wed, 05 Jun 2013 11:02:08 GMT

Hi,

Let try

sum({$<Responses.ClosingDate= {'>=$(Floor(Calendar_ResponseCreationDate.Datum))'} >}Responses.ResponsesCounter)

But I suggest to create new two fields for set analysis

LOAD

................

Floor(Responses.ClosingDate) AS [_ClosingDate],

Floor(Calendar_ResponseCreationDate.Datum) AS [_CreateDate],

................

Then use set analysis

sum({$<[_ClosingDate]= {'>=$([_CreateDate])'} >}Responses.ResponsesCounter)

Regards,

Sokkorn