Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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.
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.
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.
Okay, that is fair.
Would you mind showing an example of how this would be done using Aggr() and If()... ??
Thanks.
HI,
Try like this
=Sum(Aggr(If(Floor([Death_Date] - [surgery_1_date]) <= 7, 1, 0), PatientDimensionName))
Regards,
Jagan.
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
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)