topic Re: Set Analysis using a date data island in QlikView

Set Analysis using a date data island

Anonymous — Tue, 21 Feb 2012 04:12:55 GMT

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.

Set Analysis using a date data island

v_iyyappan — Tue, 21 Feb 2012 05:11:45 GMT

Hi ,

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

Hope its helpful for u.

Regards,

Iyyappan

Set Analysis using a date data island

jagan — Tue, 21 Feb 2012 05:24:25 GMT

Hi,

Try using the below expression

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

Regards,

Jagan.

Set Analysis using a date data island

Anonymous — Tue, 21 Feb 2012 05:35:12 GMT

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.

Set Analysis using a date data island

Anonymous — Tue, 21 Feb 2012 05:36:26 GMT

Hi Jagan,

your expresssion has not made any difference.

but thank you for offering your assistance.

thanks,

John.

Re: Set Analysis using a date data island

lironbaram — Tue, 21 Feb 2012 09:09:53 GMT

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

Re: Set Analysis using a date data island

lironbaram — Tue, 21 Feb 2012 09:12:37 GMT

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

Re: Set Analysis using a date data island

Anonymous — Tue, 21 Feb 2012 21:58:29 GMT

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.