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))) ...
Need Help
Show and Tell
Tutorial Requests
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

Bakhtawar's avatar
Bakhtawar
Post Patron
6 years ago

OK i finally posted Icey  kindly check

  • Bakhtawar's avatar
    Bakhtawar
    Post Patron
    6 years ago

    hello first i tried your formula

     

     

    VPEDate =
IF (COUNTAX ( Calender, SELECTEDVALUE ( Calender[EndQuarter]) ) > 0,MAX ( Calender[EndQuarter] ))

     

     

    here date should be display max date of month

     

     

    in qliksense

    first i created vpe_Date

     

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

     

    QLIKSENSE SCRIPT FOR PE DATE IS

     

    PE_DATE:
Load
QuarterEnd(Date('01-Jan-2015')+IterNo()-1) as PE_DATE
AutoGenerate 1 While QuarterEnd(Date('01-Jan-2015')+IterNo()-1) < QuarterEnd(Today());

     

    the logic is here like when i select any date of month like 16-nov-2018 then vpe_date should be set as max date of nov like 31-nov-2018

     

    then i create formula of earned_Factor

     

    (
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 called this earned_Factor in premium_Earned

     

    Sum(GROSS_PREMIUM * $(v_Earn_Factor))

     

     

    so how i rewrie these formuals in DAX Icey 

     

    • Bakhtawar's avatar
      Bakhtawar
      Post Patron
      6 years ago

      Icey  ????

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Bakhtawar ,

        Sorry to reply late. Please check if the below expression is the first one you want. Several others will take some time.

         

         

        VPEDate =
IF (
    COUNTAX ( Calender, SELECTEDVALUE ( Calender[Date] ) ) > 0,
    MAXX (
        FILTER (
            ALL ( Calender ),
            YEAR ( SELECTEDVALUE ( Calender[Date] ) ) = Calender[Year]
                && MONTH ( SELECTEDVALUE ( Calender[Date] ) ) = Calender[Month]
        ),
        Calender[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.