Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Here is a solution to my problem. Many thanks to @Rob Wunderlich for providing this example and guiding me towards solving my challenge. In this case, a resident load was used to create an auxiliary table. This table took the values from my four columns and combined them into one called Diagnosis Code. The load process also created a position column with the appropriate column 1 to 4. Then, a sum formula could be used to determine position.
Using this logic, I was able to plug this code method back into my app with 23 million rows and meet my end user requirements.
Hope this follow up helps someone else. !
Hi,
maybe your data model has a problem.
You should join Diagnosis Code with DiagCd2, DiagCd3 and DiagCd4.
Upload your qvw, please
Regards
MR
you can try below expression with the same dimension
Expression1
count({<DiagCd2-={''}>}[DiagCd2)
Expression2
count({<DiagCd3-={''}>}[DiagCd3)
Expression3
count({<DiagCd4-={''}>}[DiagCd4)
have you tried
count(Diag)
+
count(Diag2)
+
count(Diag3)
+
count(Diag4)
If you select any of the Diagnosis Codes in the table, it should reduce to one row with the summary for each of the columns. However, selecting the primary will always equal zero for the 2,3 and 4 columns as there can be no duplicates. The set analysis needs to consider the whole data set while matching the primary dimension for columns for 2, 3 and 4
Here is a solution to my problem. Many thanks to @Rob Wunderlich for providing this example and guiding me towards solving my challenge. In this case, a resident load was used to create an auxiliary table. This table took the values from my four columns and combined them into one called Diagnosis Code. The load process also created a position column with the appropriate column 1 to 4. Then, a sum formula could be used to determine position.
Using this logic, I was able to plug this code method back into my app with 23 million rows and meet my end user requirements.
Hope this follow up helps someone else. !