variable in formula

[Bakhtawar]

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

[Icey]

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.

[Icey]

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]

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]

Hi @Bakhtawar ,

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

Best Regards,

Icey

[Bakhtawar]

OK i finally posted @Icey  kindly check

[Bakhtawar]

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]

@Icey  ????

[Icey]

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.

[Bakhtawar]

ok but how i rewrite this in DAX

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

@Icey 

[Icey]

Hi @Bakhtawar ,

Does the above expression work?

Best Regards,

Icey

[Bakhtawar]

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]

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]

now how i call

VPEDate 

in second formula which i posted in question @Icey 

[Anonymous]

share your dataset and expected output.

Thanks & regards,

Pravin

[Bakhtawar]

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

[Anonymous]

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

[Bakhtawar]

i just want to know how i recall VPEDate in formula ?