topic Re: Subtracting to values in QlikView

Subtracting to values

sweens78 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys

I'm try to subtract the two formula below when I do them individually I get correct answer but when I try to then subtract the two formula my answer is wrong, out by 4! Any ideas where I may be going wrong? Think I may have too many counts?

Formula 1 count(if(Interventions_ID='4', NEWKEYID))-Count(if( Interventions_ID= '4', PatientsNotAssessed_ID)) correct answer

Formula 2 count(if(Interventions_ID='5', NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID)) correct answer

subtract the two?? Getting wrong answer

count(if(Interventions_ID='4', NEWKEYID))-Count(if( Interventions_ID= '4', PatientsNotAssessed_ID))-count(if(Interventions_ID='5', NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID))

Re: Subtracting to values

rubenmarin — Mon, 24 Sep 2018 11:44:07 GMT

Hi Martin, I think is a parenthesys issue:

count(if(Interventions_ID='4', NEWKEYID))-Count(if( Interventions_ID= '4', PatientsNotAssessed_ID))-(count(if(Interventions_ID='5',NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID)))

Re: Subtracting to values

trdandamudi — Mon, 24 Sep 2018 11:45:04 GMT

Can you please attach a sample app ? or give a try with the below:

count({<Interventions_ID={4}>}, NEWKEYID)-Count({<Interventions_ID={4}>}, PatientsNotAssessed_ID)-count({<Interventions_ID={5}>}, NEWKEYID)-Count({<Interventions_ID={5}>}, PatientsNotAssessed_ID)

Re: Subtracting to values

sweens78 — Mon, 24 Sep 2018 11:52:57 GMT

Great Job Ruben!

Re: Subtracting to values

sweens78 — Mon, 24 Sep 2018 12:08:39 GMT

Ruben,

I just want to get a percentage of the above as well im trying to do it this way but still no good any

thoughts?

=NUM((count(if(Interventions_ID='4', NEWKEYID))-Count(if( Interventions_ID= '4', PatientsNotAssessed_ID))-(count(if(Interventions_ID='5',NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID))) / ((Count(NEWKEYID)-Count(PatientsNotAssessed_ID)),'##.##%'))

Re: Subtracting to values

rubenmarin — Mon, 24 Sep 2018 13:05:10 GMT

Hi, if you want the percentaje of Intervention_ID 4 over the ID 5, the second part of division needs the same check to retrieve only the ID 5.

(count(if(Interventions_ID='4', NEWKEYID))-Count(if( Interventions_ID= '4', PatientsNotAssessed_ID))-(count(if(Interventions_ID='5',NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID)))) / (count(if(Interventions_ID='5',NEWKEYID))-Count(if( Interventions_ID= '5', PatientsNotAssessed_ID)))

Also, the use of 'If()' in this expression can be improved using set analysis:

"count(if(Interventions_ID='4', NEWKEYID))" can be "Count({<Interventions_ID={'4'}>} NEWKEYID)"