Variable Holding Expression Expansion issue

Brip51 — Wed, 02 Oct 2024 15:49:27 GMT

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

Re: Variable Holding Expression Expansion issue

Kushal_Chawda — Wed, 02 Oct 2024 16:08:21 GMT

@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 "="

Re: Variable Holding Expression Expansion issue

Brip51 — Thu, 03 Oct 2024 14:13:04 GMT

Hi Kushal,

Thank you ! I did not realize this.

Worked perfectly after the update you recommended

Best Regards,

Brian

topic Variable Holding Expression Expansion issue in App Development

Variable Holding Expression Expansion issue

Re: Variable Holding Expression Expansion issue

Re: Variable Holding Expression Expansion issue