Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a calculated dimension with below if condition.
Dimension:
=if(Denial_Code_Experimental = 1, 'Experimental',
if(Denial_Code_Medical_Necessity = 1, 'Medical_Necessity',
if(Denial_Code_NonCovered_Service = 1, 'Non_Covered_Service',
if(Denial_Code_Missing_Info = 1, 'Missing_Info',
if(Denial_Code_Prior_Authorization = 1, 'Prior_Authorization')))))
Measure:
Sum(aggr((Denial_Code_Appeals_Made), Accession_ID))
My requirement is for some Accession_IDs we have value for 2 or more denial codes conditions as '1'. So, when that is the case I need to sum the expression for all the conditions that has value '1'. The denial_code_experimental, denial_code_medical_necessity ....... are 5 different columns.
How can I change my condition to meet the requirement?
Please help.....
Thanks,
Rohini. M
I'm not sure I follow. Can you post some sample data and what you want your chart to look like for that sample data?
But... maybe this?
sum(aggr(nodistinct Denial_Code_Appeals_Made, Accession_ID))
Hi John,
Thanks for your reply.
Sample result table:
Type | Appeals_Made |
79282 | |
Experimental | 12016 |
Prior_Authorization | 5643 |
Medical_Necessity | 40263 |
Non_Covered_Service | 14564 |
Missing_Info | 6796 |
So here the appeals made is calculated based on the 'if' dimension condition. So, for some accession_IDs we have the denial_code values as '1' for 2 or more columns. so, I need to sum the value for all the columns that has value as '1'. But, based on the if condition I wrote if one accession_ID has denial_code_experimental as '1', then the accession_ID is not checked for other columns(Denial_code_Medical_Necessity,...). But I need to check all accession_ID's for all the conditions and I need to display the result as shown above. I know that if condition doesn't work for this requirement. pls let me know if we can do this in any other way in qlikview
I'm guessing you need another table in your data model. Something like this:
Denial_Code, Denial_Code_Note
A,Experimental
A,Medical_Necessity
B,Prior_Authorization
C,Experimental
D,Prior_Authorization
D,Missing_Info
etc.
Then set your chart up like this:
Dimension = Denial_Code_Note
Expression = sum(Denial_Code_Appeals_Made)
Again, sample data and what you want your chart to look like would help nail it down. Like maybe it's an Accession_ID instead of Denial_Code in the table. I'm unclear how it all fits together.
OK, that's the RESULT table. What does the DATA table look like?
Continuing to guess, I'll guess your data looks something like the top table here, and that for that data, you would want the results in the bottom right table.
If so, here's example script to build the additional table that makes it easy to build the result table.
Denial_Notes:
CROSSTABLE (Denial_Note, Denial_Flag)
LOAD
Accession_ID
,Denial_Code_Experimental as Experimental
,Denial_Code_Medical_Necessity as Medical_Necessity
,Denial_Code_Non_Covered_Service as Non_Covered_Service
,Denial_Code_Missing_Info as Missing_Info
,Denial_Code_Prior_Authorization as Prior_Authorization
RESIDENT Accessions
;
INNER JOIN (Denial_Notes)
LOAD 1 AS Denial_Flag
AUTOGENERATE 1
;
DROP FIELD Denial_Flag;
See attached.
Thank You John. It Worked!