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
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

  • Bakhtawar's avatar
    Bakhtawar
    Post Patron
    6 years ago

    first varaible is VPE_Date

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

    in this formula i think the logic is if we select any date   from dec i.e. '2-Dec-2019' then PEDate will set max '31-Dec-2019'  .. same goes for other month if we select date 12-Nov-2015 then PE DATE will set max as 30-Nov-2019..

     

    and your logic is working fine when i just try this ..

     

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

     

    PE_Date is calculated in script like this (THIS IS QLIKSENSE SCRIPT)

    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());

     

    then after this  i  should called this varaible "VPE_Date "  in Ear_Factor formula..

    (
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 "Ear_Factor " in Premium_earn formula  Icey 

    Sum(GROSS_PREMIUM * $(vEarnedFactor))

     

  • 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  ????