totals for calculated dimension in set analysis

[#1]

Hi Experts ,

I am using a calculated dimension like :

Pick( Dim,Zone&' '&ProdType, Total )

Inline for zone :

Dim_Zone:
Load * Inline [
ZONE_CODE, Zone
1,NAZ
1M,MEX
2N,LAN
2S,LAS
3,EUR
5,APAC
6,ABII
];

Inline for Dim :

Dim:
LOAD * Inline [
Dim
1
2

];

I am using the below expression but the totals are not visible when I select zones :

sum(
aggr(
((((1+(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'},ProdType={'Beer'},Zone=$::Zone>}FIN___ORGANIC_GROWTH_RM)
/(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'},ProdType={'Beer'},Zone=$::Zone>}FIN_PREV_AMENDED_RM))))
/
(1+(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)},ProdType={'Beer'},Zone=$::Zone>}VOL___ORGANIC_GROWTH_RM)
/(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)},ProdType={'Beer'},Zone=$::Zone>}VOL_PREV_AMENDED_RM)))))-1 )
*
(((sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'},ProdType={'Beer'},Zone=$::Zone>}FIN_PREV_AMENDED_RM))/
(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)},ProdType={'Beer'},Zone=$::Zone>}VOL_PREV_AMENDED_RM))
)*1000))

*

(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)},ProdType={'Beer'},Zone=$::Zone>}VOL_PREV_AMENDED_RM))
,COUNTRY)
)

When I change the pick condition to : Pick( Dim,Total,Zone&' '&ProdType ) ,the total shows but the zones stop displaying .

I guess the issue is with the aggr function .

Kindly help .

[sunny_talwar]

Is there a reason for swapping the dimensions in the Pick statement? Also would you be able to share a sample to look at the issue?

[#3]

Hi Sunny ,

I was swapping the dimension for debugging the issue , if you see the doc I have attached - I am able to get proper value when I select the dimensions individually but when I select both , the total comes as 0.0% .

I can't share the sample as file is placed on client n/w .

Interesting thing is that when I put $ before sum , I am able to get the total but then the individual zone values give wrong result :

$(=sum(aggr(((((1+(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'}>}FIN___ORGANIC_GROWTH_RM)
/(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'}>}FIN_PREV_AMENDED_RM))))
/
(1+(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)}>}VOL___ORGANIC_GROWTH_RM)
/(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)}>}VOL_PREV_AMENDED_RM)))))-1 )
*
(((sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={'IM-010-ST'}>}FIN_PREV_AMENDED_RM))/
(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)}>}VOL_PREV_AMENDED_RM))
)*1000))

*

(sum({$<VARIANCE_BASE_SK={'BU'},ACTUALITY_SK={'AC'},_RM_FLAG = {'Y'},FINANCIAL_ACCOUNT_KEY ={$(vVOL_FIN_KEY)}>}VOL_PREV_AMENDED_RM))
,COUNTRY))