Variable Holding Expression Expansion issue

[Brip51]

Hi,

I have an expression that I load into a variable (from Excel), but when I use that variable in a pivot table expression it does not populate correctly.  I get a single total across dimensions (I am guessing this is a variable expansion issue).

The expression is

=(
((
((Num(Sum({$<[Cash Booking Date] = , [Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-2)&12)>=$(=Max([To Book Year]-2)&01)"}>}[Earned Premium Est Inc])/ (Divider),'#,##0.')
+
Num(Sum({$<[Cash Booking Date] = , [Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)>=$(=Max([To Book Year]-1)&01)"}>}[Earned Premium Est Inc])/ (Divider),'#,##0.')))
-
Num(Sum({$<[Booking Date] =, [Cash Booking Date] = ,[Cash Booking Date Year] ={'2021'}+{'2022'}>}cash_premium)/ (Divider),'#,##0.'))
)

-
(
(Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-2)&12)>=$(=Max([To Book Year]-2)&01)"}>}[Acquisition Expense Est Inc])/ (Divider),'#,##0.')
+
(Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-2)&12)>=$(=Max([To Book Year]-2)&01)"}>}pd_treaty)/ (Divider),'#,##0.')
-
Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-2)&12)>=$(=Max([To Book Year]-2)&01)"}>}dac_treaty)/ (Divider),'#,##0.'))
)

+

(Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)>=$(=Max([To Book Year]-1)&01)"}>}[Acquisition Expense Est Inc])/ (Divider),'#,##0.')
+
(Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)>=$(=Max([To Book Year]-1)&01)"}>}pd_treaty)/ (Divider),'#,##0.')
-
Num(Sum({$<[Cash Booking Date] = ,[Booking Date] = ,business_effective_year_month = {"<=$(=Max([To Book Year]-1)&12)>=$(=Max([To Book Year]-1)&01)"}>}dac_treaty)/ (Divider),'#,##0.'))
)

-

Num(Sum({$<[Booking Date] =, [Cash Booking Date] = ,[Cash Booking Date Year] ={'2021'}+{'2022'}>}cash_acquisition_cost)/ (Divider),'#,##0.')
)
)

If I use the expression I get the correct results for "Opening balance insurance contract liabilities" -

If I use the variable with dollar expansion $(vIFRSLRCOpeningBalLB) I get the total on each row- 

I did try  $(=vIFRSLRCOpeningBalLB).sam result

I am not sure what is going on.

Any ideas where I am going wrong with my expression variable ?

Thank You,

Brian

[Kushal_Chawda · Accepted solution]

@Brip51  It's because your expression starts with "=" in your variable. So your expression is evaluating inside variable and storing the value which is getting repeated across dimension values. You need to remove initial "=" 

[Brip51]

Hi Kushal,

Thank you !  I did not realize this.  

Worked perfectly after the update you recommended

Best Regards,

Brian