Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Multiple Criteria IF Formula

Hi Experts   How would you do the following.  If PrimaryYearstoOUtcome is 5.0 then 5.0 if PrimaryYearstoOUtcome is 5.1 and we have a 5.0 (in data set as shown) then leave 5.1 as 5.1   if(STEMBRA...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Try something like this

If(PrimaryYearstoOUtcome =5.0 , 5.0 ,if(PrimaryYearstoOUtcome =5.1,5.0,PrimaryYearstoOUtcome ))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Amitchandak.

 thanks for the excellent feedback, just one slight issues when i have primaryyearstooutcome as 0.0 i am getting a blank in my Year(time) measure in the column... here is the full measure

Years (Time) = 
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 1.0 , 1.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 1.1, 1.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 2.0 , 2.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 2.1, 2.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 3.0 , 3.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 3.1, 3.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 4.0 , 4.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 4.1, 4.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),   
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 5.0 , 5.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 5.1, 5.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 6.0 , 6.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 6.1, 6.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 7.0 , 7.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 7.1, 7.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 8.0 , 8.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 8.1, 8.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 9.0 , 9.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 9.1, 9.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 10.0 , 10.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 10.1, 10.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 11.0 , 11.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 11.1, 11.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 12.0 , 12.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 12.1, 12.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 13.0 , 13.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 13.1, 13.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 14.0 , 14.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 14.1, 14.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 15.0 , 15.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 15.1, 15.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 16.0 , 16.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 16.1, 16.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] && If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 17.0 , 17.0 , If(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] = 17.1, 17.0, STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )),
         STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS])))))))))))))))))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] is number or can be compared as number , are you trying to round. Then you can try like this

     

     if(STEMBRANDYEARS[STEMBRAND] = STEMBRANDYEARS[STEMBRAND] 
		 ,If((STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] >0.0 && STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS]<1.0 ) || isblank(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS]))
		 ,0.0 , round(round(STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS],0),1))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      brillant thanks you,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

       

      when i put the measure into a table i am not getting the correct values.... let me share a sample file you'll see the formula is adding up all the values in the primaryyearsto out from 0.0 to 1.0 as opposed to just show the values at data point 0, 1, 2, 3, 4, and so hence my first formula and or you could say we are subtracting the data points as we move from 0.0 year onwards....

      ref to tab Duplicate of (Kaplan-Meier)

      and table Stembrandyears. 

       

      i am trying to get the to the right as produce by R.

      https://www.dropbox.com/s/s4wj3qzpblasw66/Kaplan_Miers_v1.2.pbix?dl=0

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        I think you are referring to this. What are the values expected in this table