Modelling, Calculated values

[#1]

Hi All,

I have been struggling to find a solution for a problem which is explained in the attached QVW file here.

It is something to do with calculating and storing values by ignoring few joins. Please suggest me a way to resolve this.

Thanks.

[sunny_talwar]

What is the output that you are expecting to see? Can you elaborate on this?

[#3]

I have uploaded the sample application with added dimensions, the numbers when USD or FXn buttons are selected should be the same in the 'Actual' table. My Set expressions for PY which is Prior Year is very similar to the Current Year CY.

[sunny_talwar]

This expression seems to have worked for me

=Num(Sum(Aggr(if(vCurr='USD',

Sum({<FISC_YR_NBR={"$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_USD_AMT),

  if(vCurr='FXN',Sum(Aggr(Sum({<FISC_YR_NBR={"$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_LCY_AMT)

*(Sum(TOTAL<FISC_MTH_NBR>{<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"},

  CTRY_NAM,PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

  /Max(TOTAL<FISC_MTH_NBR>{1<FISC_YR_NBR={"$(=vFXPrevYrToday)"}>}FISC_DAY_OF_MTH_NBR)),

  PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FISC_MTH_NBR)))), PTY_VEND_GP_DES)),'#,###,###,##0')

[#5]

The numbers when we switch between USD and FXn should be the same for the country "US" and it is working as expected for the CY column. Please refer the screens below, the CY column will not change if we switch between USD and FXN which is right. It is deviating only for the PY column here. Moreover what will happen if i introduce a cyclic dimension to this table? Should it br grouped on all the different dimension of the group?

Thanks in advance Sunny.

[sunny_talwar]

So this is more like an issue with your current expression in the straight table also? Because based on my selection in USD OR FXn, I am seeing the numbers changing in the straight table also. Which of the two numbers are the correct numbers? 7,900,... or 8,035,...?

You can handle Cycle Group using GetCurrentField(GroupName) within the dollar sign expansion

$(='[' & GetCurrentField([GroupName]) & ']') in place of PTY_VEND_GP_DES in the Aggr() expression above