Forum Discussion

Bakhtawar's avatar
Bakhtawar
Post Patron
6 years ago
Solved

dax formula

I have this formula in qliksense

(
RangeMax(0,RangeMin(( POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1),(Date(vPE_Date) - POLICY_EFFECTIVE_DATE +1)))
)
/
(POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1)

i am trying to rewrite this dax formula

 

vEarned_Factor = (CALCULATE(Max(0,min('Members'[POLICY_END_DATE]) - min('Members'[POLICY_EFFECTIVE_DATE])),(ENDOFQUARTER - 'Members'[POLICY_EFFECTIVE_DATE]+1))) /'Members'[POLICY_END_DATE] -  'Members'[POLICY_EFFECTIVE_DATE

 

But this some errors

 

 

any idea how to rewrite this ?

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

     One of them will give you result. That qlik funtion gives min max out of two, Tried that

     

    measure =
var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)
return

divide(if(max(_d1) <max(_d2),max(_d1),max(_d2)),max(_d1))

     

    Or

    measure =
var _d1 =(datediff( POLICY_END_DATE,POLICY_EFFECTIVE_DATE,day)+ 1)
var _d2 = (datediff([vPE_Date].Date,POLICY_EFFECTIVE_DATE) +1)
return

divide(if(sum(_d1) <sum(_d2),sum(_d1),sum(_d2)),sum(_d1))

     

11 Replies

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      this part is related to earned days .. like how much days we earned .

      RangeMax(0,RangeMin(( POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1),(Date(vPE_Date) - POLICY_EFFECTIVE_DATE +1

       

      and this part is realted to total days

       

      POLICY_END_DATE-POLICY_EFFECTIVE_DATE+ 1

      ..  amitchandak