Cross Table Custom Expression

[Sony Hejmady]

I have requirement forCOMP Threshold but the expression in 3 rd point is not giving me the expected result.My requirement is that when Current month balance is greater than previous month balance then Previous Month * 1.1.

So I triedMax(Case when [Current Period]>[Previous Period] then [Previous Period] * 1.1 END) as [COMP Threshold] which did not work as seen in extract.

Custom Expression and Calculated column details:

1.Sum([Current Period]) as [Current Month],

where Current Period (Real datatype) is calculated column= case when [period_int]=Max([period_int]) then [Local_Cur_Sum] end

whereperiod_int=Integer([period]) is caluclated column as Period was String.

Where Local_Cur_Sum=when [Courrency Type]="local_currency_code" then [Local_closing_balance]

when [Courrency Type]="transaction_currency_code" then NULL END

The table has been unpivoted for local_currency_code andtransaction_currency_code.

 

2.Sum([Previous Period]) as [Previous Month]where Previuous Period (Real data type) is calculated column=case when [period_int]=Max([period_int] - 1) then [Local_Cur_Sum] end

 

3.Max(Case when [Current Period]>[Previous Period] then [Previous Period] * 1.1 END) as [COMP Threshold]

[Fabian Duerr]

Could you provide a sample data set so that we can understand the structure of the underlying data table and how you set up the cross table