topic Re: set analysis date comparison in QlikView

set analysis date comparison

Tue, 07 May 2013 15:42:21 GMT

Hi,

I'm having trouble comparing dates using set analysis:

I need to count the following:

1) Number of patients where [Death_Date] is 7 days or less after [surgery_1_date]

2) Number of patients where [Death_Date] is between 8 days post [surgery_1_date] and [surgery_2_date]

3) Number of patients where [Death_Date] is after [discharge date] but before [surgery_2_date] ONLY WHERE [admit_type] ='1'

the best i have is:

=count({$<DEATH_DATE = {"<[surgery_1_date]"}>} PAT_ID)

but i can't figure out how to intergrate the more elaborate conditions.

please help!

Thanks!!

Re: set analysis date comparison

jagan — Tue, 07 May 2013 16:26:26 GMT

Hi,

Do the calculation in Script like below

LOAD

If(Floor([Death_Date] - [surgery_1_date]) <= 7, 1, 0) AS 7DaysOrLess,

If(Floor([Death_Date] - [surgery_2_date]) <= 8, 1, 0) AS 8DaysOrLess,

If(([Death_Date] > [discharge date]) AND ([discharge date] < [surgery_2_date]), 1, 0) AS AfterDischarge

FROM DataSource;

Now for

1) Sum(7DaysOrLess)

2) Sum(8DaysOrLess)

3) Sum({<[admit_type] ={'1'} >} AfterDischarge)

Hope this helps you.

Regards,

Jagan.

Re: set analysis date comparison

Tue, 07 May 2013 16:46:00 GMT

thanks for your help. i am wondering, though why we can't accomplish this in SET. For example, do you know what is wrong with this? It should work....

=count({$<DEATH_DATE = {"<{$(=([SURG_DATE]+7))}"} >} PAT_MRN_ID)

that should be counting the PatID, where Death Date is < (surgery+7 days)

but its not working... don't know why.

Re: set analysis date comparison

jagan — Tue, 07 May 2013 16:49:06 GMT

Hi,

We can do this by using the Aggr() and If(), but it has performance issues, better calculating in script itself, so that we can avoid huge expressions.

Hope it helps you.

Regards,

jagan.

Re: set analysis date comparison

Tue, 07 May 2013 17:17:47 GMT

Okay, that is fair.

Would you mind showing an example of how this would be done using Aggr() and If()... ??

Thanks.

Re: set analysis date comparison

jagan — Wed, 08 May 2013 05:14:00 GMT

HI,

Try like this

=Sum(Aggr(If(Floor([Death_Date] - [surgery_1_date]) <= 7, 1, 0), PatientDimensionName))

Regards,

Jagan.

Re: set analysis date comparison

ablaas — Thu, 15 May 2014 09:50:59 GMT

Hi,

I just found a document where there is a similar example, using set analysis:

Sum({<KeyAutoNumber = {"=(DayDelivery < DayOrder -7)" } >} Sales)

You can find it in: Set Analysis: syntaxes, examples

In the PDF, page 14

Re: set analysis date comparison

ablaas — Thu, 15 May 2014 09:53:16 GMT

I think your problem is in the set syntaxis.

You have to use a Key as the searched dimension.

Check the document in Set Analysis: syntaxes, examples, page 14 in the PDF (thanks Fabrice Aunez)

Using two fields

If we want to select a dimension through two fields, for example:

- Billed client = Delivered Client

- Ordering Date = Delivery Date

- …

The syntax is more or less the one seen in 4.6.2. section:

Syntax: {<Dimension = {"=boolean condition"}>}

The boolean condition will be created by comparing the two fields.

Attention: the searched dimension cannot be also in the boolean condition. If needed, create an

integer key with Autonumber().

We want to get the sales that have been delivered the same day. We have two fields : DayDelivery and

DayOrder

Sum({<KeyAutoNumber = {"=(DayDelivery=DayOrder)" } >} Sales)

And we can easily get the sales with a late delivery, for example 7 days or whatever stored into a variable:

Sum({<KeyAutoNumber = {"=(DayDelivery < DayOrder -7)" } >} Sales)

Sum({<KeyAutoNumber = {"=( DayDelivery < DayOrder -$(vDelay))" } >} Sales)