topic Re: Total for All Rows in QlikView

Total for All Rows

Thu, 02 Apr 2015 12:02:05 GMT

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,

Re: Total for All Rows

PrashantSangle — Thu, 02 Apr 2015 12:07:43 GMT

Hi,

Tryl with TOTAL<DimensionName>

try like Sum(TOTAL<AgencyName> MTD)

Regards

Re: Total for All Rows

pokassov — Thu, 02 Apr 2015 12:10:36 GMT

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

Re: Total for All Rows

buzzy996 — Thu, 02 Apr 2015 12:25:05 GMT

use this ,load ur data accordinlgy

Re: Total for All Rows

Thu, 02 Apr 2015 12:41:24 GMT

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,

Re: Total for All Rows

Thu, 02 Apr 2015 12:45:33 GMT

Hi,

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

Re: Total for All Rows

buzzy996 — Thu, 02 Apr 2015 13:01:49 GMT

see this,providing ur o/p.

Re: Total for All Rows

Thu, 02 Apr 2015 14:06:02 GMT

Thanks Krishnama.

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

Regards,

Re: Total for All Rows

Thu, 02 Apr 2015 14:14:44 GMT

Hi,

can you require like this.

Re: Total for All Rows

buzzy996 — Thu, 02 Apr 2015 14:16:08 GMT

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

Re: Total for All Rows

Thu, 02 Apr 2015 14:17:06 GMT

Something like this -

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

Column Full month Projn is a based on below condition -

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

Re: Total for All Rows

Thu, 02 Apr 2015 14:23:29 GMT

Thanks Shiva.

The result i am looking for is

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

To get this I am using Aggr function and it is not working with the if condition where; for a particular agency if it does not have any business last year (column PY MTD) the full month projn should be equal to MTD column and then want the summary of all row values.

It works fine if I have agency with prior year values. and comes the total as 9.2 but what I want is the values for agency ABC12349 and ABC12364 .3 each in MTD column to be considered while calculating Full Month Projn total.

Re: Total for All Rows

buzzy996 — Thu, 02 Apr 2015 14:30:00 GMT

=Round(If(Dimensionality() = 0,sum(if(PYMTD >0, (MTD*FullMonthPY)/PYMTD,MTD))),0.1) --use this expression for "Full Month Projn"!

Re: Total for All Rows

buzzy996 — Thu, 02 Apr 2015 14:42:01 GMT

if ur happy with tht,can u close this thread?