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 ,

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

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