Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am stumped as to why I can't get this to work. See Attachment.
My red table is suppose to be a count of discharged customers from the site. The set analysis looks to count the number of patientID's where the DischargeDateId matches the DateId.
The DateId is the primary key of my date lookup table, other elements of this table are available in the list boxes at top of screen (FinancialYear, MonthNameShort).
The red table seeks to display the count of discharges split by month and financial year. In Australia our financial year starts in July. but don't worry too much about that.
the syntax for the expression is: COUNT ( {<[DischargeDateId]=P([DateId])>} DISTINCT InpatientEpisodeId)
What am I doing wrong?
Regards,
John.
Hi ,
Just mapped to DateId, DischargeDateid using the expression like = Count(DISTINCT InpatientEpisodeId).
Hope its helpful for u.
Regards,
Iyyappan
Hi,
Try using the below expression
COUNT( {$< DischargeDateId={$(=Concat(DISTINCT DateId, ','))}>} InpatientEpisodeId)
Regards,
Jagan.
Hi Iyyappan,
Yes I can map the DischargeDateId to DateId during load but also need to count by AdmissionDateId and there will be other Dates I need to count or aggregate by.
I am looking to use the power of Data islands to avoid duplication or over complication of processes.
thanks,
John.
Hi Jagan,
your expresssion has not made any difference.
but thank you for offering your assistance.
thanks,
John.
hi john
try the attach example
your expression won't work because al the dimmensions in your tables are from data island
so what happens the appliction filters only the records that are in the selected dateid list but
it count all the records in all the cells becuase the is no connection between the dimensons in the red table and the data
so i change the expression to if expression
hi john
try the attach example
your expression won't work because al the dimmensions in your tables are from data island
so what happens the appliction filters only the records that are in the selected dateid list but
it count all the records in all the cells becuase the is no connection between the dimensons in the red table and the data
so i change the expression to if expression
Hi Liron,
you are correct that my problem stems from the fact that I am trying to use elements of the data island in my dimensions.
Your expression, while correct in the current context, fails when more elements are added, ie: multiple years. I'm sure there is a way round this but it would just add to the complexity of your expression and I like things to be simple and clean where possible.
As Iyyappan suggested above, I probably need to map the Date reference table in, but because I have several different dates in my table that I need to count by (ie: Admission date - how many admissions per month, Discharge Date - how many discharges per month). I probably need to look at using the existing Dimension/Fact table and building a new table with each element in its own right.
ie:
ActivityTable:
LOAD
'Admission' AS IdentifyType
AdmDate AS DateValue
WHERE NOT(ISNULL(AdmDate))
RESIDENT MyTable
then do the same for Discharges.
This gives me a clean table with 1 row for either type. I can then use Set Analysis on the [IdentifyType] to restrict my count by month, year etc.
------------
The reason I want to avoid this second method is because I have done it before and it starts to get messy after a while due to all of the additional elements that are loaded in. ie: patient gender, age, admission method and so forth. it is a long list. What do I load with the admission date? what do I load with the discharge date? how do I count on these elements later on? how do I remember whether I assigned patient age to the admission record or discharge record? and so on.
having 1 table with 1 row of information for each patient visit is much cleaner.
Thank you,
John.