5 Replies Latest reply: May 14, 2015 8:34 AM by Mike Czerwonky RSS

    Straight Table - need set analysis for summary columns

    Mike Czerwonky


      Greetings,

       

      I have a fact table that has 4 fields.  Diagnosis Code, Diagnosis Code 2, Diagnosis Code 3 and Diagnosis Code 4.  See attached example picture. I would like to create a straight table that gives me the sum of the number of times each code is used in each column.  In my example there are 59805 rows and code 7291 is used 74 times as the Primary Diagnosis.  If you look at the three boxes below in the picture, you can see that 7291 was used 356 as a secondary diagnosis, 321 as a third diagnosis and 375 as a 4th diagnosis.  I would like my straight table to shows those number across the row and allow me to get a total number of conditions where a patient was diagnosis with the code.

       

      My dimension is Diagnosis Code and the Count of Primary is a simple =count([Diagnosis Code])

       

      For example, the first row of the table might have

       

      Diagnosis Code 7291, Secondary Code 4019, Third Code as 2724 and Forth Code as 2449

       

      The code numbers will always be unique on the same row.

       

      My brain can't come up with the logic to complete the summary formulas for Diag Code 2, 3 and 4.

       

      Please help if you have the answer.

       

      Thanks,

       

      Mike

       

      Message was edited by: Mike Czerwonky Here is a copy of my QVW sample.  This should give you what you need to see what I am looking to do !