Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
=Round(If(Dimensionality() = 0,sum(if(PYMTD >0, (MTD*FullMonthPY)/PYMTD,MTD))),0.1) --use this expression for "Full Month Projn"!
Hi,
Tryl with TOTAL<DimensionName>
try like Sum(TOTAL<AgencyName> MTD)
Regards
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
use this ,load ur data accordinlgy
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,
Hi,
If You can require below Straight , I will send to you.
see this,providing ur o/p.
Thanks Krishnama.
But i need the summary without dimension and total of all rows in Full Month Projn.
Regards,
Hi,
can you require like this.
this is not what u want?,pls check and let me know