7 Replies Latest reply: Feb 21, 2012 4:58 PM by John Anderson RSS

    Set Analysis using a date data island

    John Anderson

      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
          Iyyappan v

          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
              John Anderson

              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
              jagan mohan rao appala

              Hi,

               

              Try using the below expression

               

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

               

              Regards,

              Jagan.

                • Set Analysis using a date data island
                  John Anderson

                  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
                      liron baram

                      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
                        liron baram

                        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
                            John Anderson

                            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.