Total for All Rows

[#1]

Hi,

I am trying to create summary table based on the below data using Aggr() function however i do not want to show the dimension.

Data table -

Agncy Name	MTD	Full Month PY	PY MTD	Full Month Projn
	£7.2	£8.2	£5.9	£9.2
ABC12345	£0.3	£0.4	£0.3	£0.4
ABC12346	£1.4	£1.1	£0.8	£2.0
ABC12347	£0.2	£0.2	£0.1	£0.2
ABC12348	£0.0	£0.1	£0.0	£0.0
ABC12349	£0.3	£0.0	£0.0	-
ABC12350	£0.0	£0.0	£0.0	£0.0
ABC12351	£0.0	£0.0	£0.0	£0.0
ABC12352	£0.1	£0.2	£0.1	£0.1
ABC12353	£0.4	£0.5	£0.4	£0.5
ABC12354	£2.1	£3.6	£2.7	£2.8
ABC12355	£1.7	£1.1	£0.8	£2.4
ABC12356	£0.0	£0.1	£0.0	£0.1
ABC12357	£0.0	£0.0	£0.0	£0.0
ABC12358	£0.0	£0.0	£0.0	£0.0
ABC12359	£0.0	£0.0	£0.0	£0.0
ABC12360	£0.0	£0.0	£0.0	£0.0
ABC12361	£0.0	£0.1	£0.1	£0.1
ABC12362	£0.3	£0.4	£0.3	£0.4
ABC12363	£0.1	£0.1	£0.1	£0.1
ABC12364	£0.3	£0.0	£0.0	-
ABC12365	£0.0	£0.1	£0.1	£0.0
ABC12366	£0.0	£0.1	£0.1	£0.0
ABC12367	£0.0	£0.1	£0.1	£0.0

The condition to calculate the Full Month Projn column is

=if(PY MTD >0, (MTD*Full Month PY)/PY MTD,MTD)

and the desired output is -

MTD	Full Month PY	PY Mnth To Date	Full Month Projn
£7.2	£8.2	£5.9	£9.8

Can someone help me with this please?

Regards,

[PrashantSangle]

Hi,

Tryl with TOTAL<DimensionName>

try like Sum(TOTAL<AgencyName> MTD)

Regards

[pokassov]

Hi!

Can you try this four expressions without any dimension?

1. sum(MTD)

2. sum( [Full Month PY])

3. sum( [PY MTD])

4. sum( if([PY MTD]>0, MTD, MTD*[Full Month PY]/[PY MTD]))

Sergey

[buzzy996]

use this ,load ur data accordinlgy

[#5]

Hi,

Thank you for the response.

however if you see in the data agency number ABC12349 and ABC12364 have blank values for Full Month Projn column which is calculated via if condition and using Aggr function without Dimension the if condition is not working.

Regards,

[#6]

Hi,

If You can require below Straight , I will send to you.

[buzzy996]

see this,providing ur o/p.

[#8]

Thanks Krishnama.

But i need the summary without dimension and total of all rows in Full Month Projn.

Regards,

[#9]

Hi,

can you require like this.

[buzzy996]

this is not what u want?,pls check and let me know