Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a straight table in which i am using a measure expression and using Sum as the dropdown from Totals Function. I have to show the same expression in the KPI object but the KPI object does not have the option to select the Totals Function explicitly.So, i am using sum(Aggr() on the basis of dimension present in the filter.But still the values are not matching if i select one of the filter "Historic Period".
This expression used in the table contains a sub part of set-analysis which has [Historic Period]=> condition so that the values does not change on the filter selection.Please note this filter is not present as dimension in the chart.Still i added it in the dimension of SUM(AGGR() expression but then it shows the value of the KPI object as 0 .
Expression used in Table:
((sum({$<PERIOD_CLOSED_FLAG={'N'},[Historic Period]=>}[Site TotalExposure]))
-
(sum({$<POSITION_YEAR=${"=GetFieldSelections(left([Historic Period],4))"},
POSITION_QUARTER=${"=GetFieldSelections(Right([Historic Period],2))"}>}[Site TotalExposure])))
*
num((sum({$<PERIOD_CLOSED_FLAG={'N'},[Historic Period]=>}FXRate)),'#,##0.00000000000000')
Expression used in the KPI:
sum(AGGR(
((sum({$<PERIOD_CLOSED_FLAG={'N'},[Historic Period]=>}[Site TotalExposure]))
-
(sum({$<POSITION_YEAR=${"=GetFieldSelections(left([Historic Period],4))"},
POSITION_QUARTER=${"=GetFieldSelections(Right([Historic Period],2))"}>}[Site TotalExposure])))
*
num((sum({$<PERIOD_CLOSED_FLAG={'N'},[Historic Period]=>}FXRate)),'#,##0.00000000000000')
,POSITION_ID,Country,shortnamenew,LOB,[Risk Rating],[Risk Category]
)
)
I tried adding the Historic Period as a dimension but it shows 0 value after adding.I guess it is not working as i am using [Historic Period]=>.
Is there any other way to achieve this or in case if i am missing anything.
May be you need to ignore selection in Historic Period in the out aggregation also
Sum({<[Historic Period]>} Aggr( ......))
Would you be able to share an image or a sample to see what you have?
May be you need to ignore selection in Historic Period in the out aggregation also
Sum({<[Historic Period]>} Aggr( ......))
So, how does that matter? Inner aggregation will continue to honor it, but outer will ignore...
Thanks a lot Sunny.This solution worked.
I have another requirement on the same field.I have to display 2 measure in walk chart as increase and decrease.Increase will show the the sum of all the positive values derived from the Exposure excluding FX column of the table and decrease will show the sum of all negative values derived from the Exposure excluding FX column of the table.
I am using the same sum(AGGr) expression used in the KPI object but it as we are using AGGR , it aggregates into a single value and if the value >0 it shows into the Increase measure of walkchart and decresase is 0 then.But as per the requirement we need values in both increase/decrease measure of Walk Chart.
Any suggestion on how to achieve this?
Please close this thread by marking the correct answer/s and open a new thread for new question.
Best,
Sunny