Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis using a date data island

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.

7 Replies
v_iyyappan
Specialist
Specialist

Hi ,

     Just mapped to DateId, DischargeDateid using the expression like = Count(DISTINCT InpatientEpisodeId).

Hope its helpful for u.

Regards,

Iyyappan

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try using the below expression

COUNT( {$< DischargeDateId={$(=Concat(DISTINCT DateId, ','))}>}  InpatientEpisodeId)

Regards,

Jagan.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

Hi Jagan,

your expresssion has not made any difference. 

but thank you for offering your assistance.

thanks,

John.

lironbaram
Partner - Master III
Partner - Master III

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

lironbaram
Partner - Master III
Partner - Master III

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

Anonymous
Not applicable
Author

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.