Forum Discussion

Bakhtawar's avatar
Bakhtawar
Post Patron
6 years ago

variable in formula

hi all

first i am going to you what i have in qliksense

i create a varaible VPEDate.. in that i do this

=if(GetSelectedCount(PE_DATE)>0,Date(Max(GetFieldSelections(PE_DATE))),Date(max(PE_DATE)))

then i have another varaible Ear_Factor in that i have formula is this

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

 

then i have finall a formula for Premium_earned which is like that

Sum(GROSS_PREMIUM * $(vEarnedFactor))

so how i all do this power bi .. and create final formula ..

 

any idea please

 

Need Help
Show and Tell
Tutorial Requests

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    share your dataset and expected output.

     

    Thanks & regards,

    Pravin

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      i just want to be rewrite these formulas in dax expression ..  consider xyz table for ur  own

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I don't know much about qlik.

         

        I hope getselectecount means user selected dates in slicers.

         

        VPEDate=IF(ISFILTERED(TEST1[Date]),CALCULATE(MAX(TEST1[Date]),ALLSELECTED(TEST1[Date])),MAX(TEST1[Date])

         

        Premium_earned=sum(GROSS_PREMIUM) * [vEarnedFactor]

         

        I am not sure what your second formula is doing.

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

         

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Bakhtawar ,

    I don't know much about Qlik Sense. According to my understanding, I rewritten the varaible VPEDate into the following dax expression. Please check if it is what you want.

    VPEDate = 
IF (
    COUNTAX ( 'Table', SELECTEDVALUE ( 'Table'[PE_DATE] ) ) > 0,
    MAXX (
        FILTER ( 'Table (2)', [PR_DATE] IN VALUES ( 'Table (2)'[PR_DATE] ) ),
        MAX ( 'Table (2)'[PR_DATE] )
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      now how i call

      VPEDate

      in second formula which i posted in question Icey 

       

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      AND IN THIS FORMULA

      =if(GetSelectedCount(PE_DATE)>0,Date(Max(GetFieldSelections(PE_DATE))),Date(max(PE_DATE)))

       

      getselectedcount and getfieldselections is function not tables name

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      i did not understand in this formula

       

      VPEDate = 
IF (
    COUNTAX ( 'Table', SELECTEDVALUE ( 'Table'[PE_DATE] ) ) > 0,
    MAXX (
        FILTER ( 'Table (2)', [PR_DATE] IN VALUES ( 'Table (2)'[PR_DATE] ) ),
        MAX ( 'Table (2)'[PR_DATE] )
    )
)

       

      where there table 2 ? i have only 1 table calendar .. in that i have 1 column PE_Date Icey 

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Bakhtawar ,

        Sorry, I misunderstood it. Please explain your varaibles for me. Then I'll rewritten the DAX expression.

         

        Best Regards,

        Icey

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Bakhtawar ,

    Please check:

    v_Earn_Factor =
VAR MIN1 =
    MINX (
        'calender',
        DATEDIFF ( 'calender'[POLICY_EFFECTIVE_DATE], 'calender'[POLICY_END_DATE], DAY ) + 1
    )
VAR MIN2 =
    MINX (
        'calender',
        DATEDIFF ( 'calender'[POLICY_EFFECTIVE_DATE], [VPEDate], DAY ) + 1
    )
VAR MAX1 =
    IF ( MIN1 > MIN2, MIN1, MIN2 )
VAR MAX2 =
    IF ( MAX1 > 0, MAX1, 0 )
VAR Result =
    DIVIDE (
        MAX2,
        DATEDIFF ( 'calender'[POLICY_EFFECTIVE_DATE], 'calender'[POLICY_END_DATE], DAY ) + 1
    )
RETURN
    Result

    Premium_earned = SUMX ( 'calender', GROSS_PREMIUM * [vEarnedFactor] )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.