topic Re: set analysis issue in QlikView

set analysis issue

prasadcm — Mon, 02 May 2016 07:23:37 GMT

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

Re: set analysis issue

sunny_talwar — Mon, 02 May 2016 07:25:27 GMT

Try this:

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

Re: set analysis issue

prasadcm — Mon, 02 May 2016 07:25:41 GMT

sunindia‌

Re: set analysis issue

prasadcm — Mon, 02 May 2016 07:28:59 GMT

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

Re: set analysis issue

sunny_talwar — Mon, 02 May 2016 07:30:40 GMT

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

Re: set analysis issue

prasadcm — Mon, 02 May 2016 07:34:30 GMT

Hi,

No it does give output as

If i select Mar then it gives correct answer

Re: set analysis issue

sunny_talwar — Mon, 02 May 2016 07:37:26 GMT

How about this:

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

Re: set analysis issue

sunny_talwar — Mon, 02 May 2016 07:42:31 GMT

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

Re: set analysis issue

avinashelite — Mon, 02 May 2016 07:54:18 GMT

try like this

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

Re: set analysis issue

HirisH_V7 — Mon, 02 May 2016 08:14:08 GMT

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

Re: set analysis issue

prasadcm — Mon, 02 May 2016 08:22:01 GMT

This works perfectly

Thanks a lot

Re: set analysis issue

sunny_talwar — Mon, 02 May 2016 08:23:07 GMT

No problem at all. I am glad I was able to help

Re: set analysis issue

prasadcm — Mon, 02 May 2016 08:23:42 GMT

it just gives me data related to GL Account No= {'32131.4003'},whereas i need all gl accounts