How to find part (%) of the month sum from all year sum in group (Cycle) ?

[yanivvl0]

hi ,

i have group with year->month , when i drill to month level i need to see for each month the relative part of the whole year ,for example , if i have at the year level the values :

and drill to month level , Expect see for each month the relative part (%) of the whole year (2017) :

according to the months values :

BUT instead i see for each month the relative part of the whole ALL YEARS from 1997 ...    :

I know that its because i am using TOTAL in the expresion :

sum (  A_VALUE ) /  sum ( TOTAL <A_YEAR>  A_VALUE  ) * 100 

does anybody know the right way to solve this problem  ?  THANKS

[madhumitha]

Hello Yaniv,

please try below expression

sum (  A_VALUE ) /  sum ( TOTAL {<A_YEAR =p(A_YEAR)>}   A_VALUE  ) * 100 

[yanivvl0]

HI madhumitha ,

its  still the same  Result, please note that I do not make a selection of a certain year.

[techvarun]

Try this

IF( Only(Month), sum (Value ) /  sum ( TOTAL Value  ) * 100,Sum(Value))

[yanivvl0]

hi Varun, no change , i had notice that you are not refering to the year , i think it must be part of the expresion.

[techvarun]

Check the attached App

[techvarun]

[madhumitha]

Hi,

You are using cyclic group in your dimension, which will not act like drilling.

Could you try changing it into Drill down group and check? Also can you please attached your QVD with sample?

[yanivvl0]

i see, i see, the thing is that i need to find the relative part of 1/2017 from the total sum of  year 2017 only ( and so on 2/2017 , 3/2017 .. )  ,  AND ALSO  the relative part of 1/2018 from the total sum of  year 2018 only . 

PLS see the second my picture above - its shoes months of year.

[techvarun]

Possible to provide sample data?