Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator III

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

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 :

sumA_VALUE ) /  sum ( TOTAL <A_YEARA_VALUE  ) * 100

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

1 Solution

Accepted Solutions
MVP

May be this

=If(\$(vGroup) = 'year', Sum(value), Aggr(Sum(value)/Sum(TOTAL <year> value) * 100, year, year_month_desc))

Where vGroup is GetCurrentField([Group])

12 Replies
Creator

Hello Yaniv,

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

Creator III
Author

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

Specialist II

Try this

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

Creator III
Author

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

Specialist II

Check the attached App

Specialist II

Creator

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?

Creator III
Author

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.

Specialist II

Possible to provide sample data?

Community Browser