set analysis issue

[prasadcm]

Hi,

I need something like this,

sum of gl accounts to be divided by specific gl account={32131.4003}

Dimensions are gl account no,name,year & month

I wrote expression as
= sum([Additional Currency Amount]) / sum({<[GL Account No]= {'32131.4003'}>}[Additional Currency Amount])

My output should be

But i get o/p as 

all gl accounts should be displayed.

Regards,

Prasad

[sunny_talwar]

Try this:

=Sum([Additional Currency Amount])/Sum(TOTAL{<[GL Account No]= {'32131.4003'}>}[Additional Currency Amount])

[prasadcm]

sunindia‌

[prasadcm]

Hi,

i tried this before,but if i select gl account ={32131.4003},i should get 1 for all months for that gl account since it is getting divided by itself

[sunny_talwar]

I believe it should be doing that? Is it not doing that with the expression provided above? Have you tried it?

[prasadcm]

Hi,

No it does give output as

If i select Mar then it gives correct answer

[sunny_talwar · Accepted solution]

How about this:

=Sum([Additional Currency Amount])/Sum(TOTAL<MonthField, YearField> {<[GL Account No]= {'32131.4003'}>}[Additional Currency Amount])

[sunny_talwar]

or may be this, I can't seem to think clearly right now

=Sum([Additional Currency Amount])/Sum(TOTAL<[GL Account No]> {<[GL Account No]= {'32131.4003'}>}[Additional Currency Amount])

UPDATE: The one provided above is the right one. Had to test it out to get my mind straight

[avinashelite]

try like this

Sum([Additional Currency Amount])/Sum({1<[GL Account No]= {'32131.4003'}>}[Additional Currency Amount])

[HirisH_V7]

Hi,

check this,

Data:
LOAD * INLINE [
    GL.No, GL.Name, Year, Month, Data
    1, A, 2016, Jan, 88
    2, B, 2016, Feb
    3, C, 2016, Mar, 85
    4, D, 2016, Apr
    5, E, 2016, May, 63
];

By using the Above Sample data,

Does the calculation as you desired,

Sum(Data)/Sum(Total{<GL.No={'1'}>}Data)

Look into this,

It will work when you select the particular Gl.no which you specified as it contains the  Associated data . if you select any other means that particular GL.no will not be picked and it will show as null i.e what happening your case.

So use this expression,

Sum(Data)/Sum(Total{1<GL.No={'1'}>}Data)

No selections for the Particular GL.No total.so that you can acheive your's Desired Result as above.

HTH,

PFA,

Hirish