topic Re: Difference between Sum(Price) or Total(Price) ?? in QlikView

Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 05:06:27 GMT

Hello,

I have 3 fields :

ID_No , Price , Department.

Now if i want sum of price i do:

Sum(Price) or Total(Price).

What is the difference between : Sum(Price) or Total(Price)

Thanks

Re: Difference between Sum(Price) or Total(Price) ??

jagan — Mon, 13 Oct 2014 05:16:48 GMT

Hi Nikhil,

For getting Sum of Price use

=Sum(Price), when you use this you will get the dimension wise price values

For ex:

Country, Sum(Value)

India, 10000

Srilanka, 5000

If you Sum(Total Value), it ignores the dimensions and displays sum of value in all rows for the above example

Country, Sum(TOTAL Value)

India, 15000 (10000 + 5000)

Srilanka, 15000 (10000 + 5000)

Hope this helps you

Regards,

jagan.

Re: Difference between Sum(Price) or Total(Price) ??

anbu1984 — Mon, 13 Oct 2014 05:17:13 GMT

There is no function named Total. Do you mean Sum(Price) vs Sum(Total Price)?

Re: Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 05:20:27 GMT

HEy,

If i am using AVG(). Let Say:

Avg(Sum(Price)) and Avg(Total Price) , Then what is the difference ??

Thanks

Re: Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 05:20:45 GMT

HEy,

If i am using AVG(). Let Say:

Avg(Sum(Price)) and Avg(Total Price) , Then what is the difference ??

Thanks

Re: Difference between Sum(Price) or Total(Price) ??

anbu1984 — Mon, 13 Oct 2014 05:30:15 GMT

Load * Inline [

OrderId,Date,Amount

1,1/1/2014,200

1,1/1/2014,100

2,1/1/2014,300

2,1/1/2014,400

];

Dimension is OrderId

Avg(Amount) for OrderId 1 is (100+200)/2 = 150

Avg(Total Amount) - (100+200+300+400 )/4=250 -- Total ignores dimension and calculates Avg for entire table

Avg(Amount) for OrderId 2 is (300+400)/2 = 350

Avg(Total Amount) - (100+200+300+400 )/4=250

Re: Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 05:49:57 GMT

Hey,

But i have asked for :

Avg(Sum(Price)) and Avg(Total Price)

And you ahve told me about :

Avg(Price) and Avg(Total Price)

So, does that mean : Avg(Sum(Price)) and Avg(Price) are same ??

Re: Difference between Sum(Price) or Total(Price) ??

jagan — Mon, 13 Oct 2014 06:04:05 GMT

Hi,

You will get error if you use =Avg(Sum(Amount)), you cannot use two Aggregation functions, so Avg(Price) is valid.

Hope this helps you.

Regards,

Jagan.

Re: Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 06:08:18 GMT

HEy,

So it means ,

If i use Avg(Price): It will give me Avg as per dimension that i have selected on X-Axis

If i use Avg( Total Price): It will give me Avg irrespective of dimensiont hat i have used on X-Axis.

Right ??

And if i use Avg(Total Price) and select a dimension, then i have read somewhere that it will give dimensionwise Avg . Right ???

Re: Difference between Sum(Price) or Total(Price) ??

jagan — Mon, 13 Oct 2014 06:17:46 GMT

Hi,

Let us take one example,

Dept, Sales

1, 100

1, 200

2, 300

4, 400

In chart if you have Dept as Dimension,

If you use Avg(Sales) then you will get

Dept, Avg(Sales)

1, 150 ---------------(100 + 200)/2 = 150

2, 300

4, 400

If you use Avg(TOTAL Sales) then you will get

Dept, Avg(TOTAL Sales)

1, 250

2, 250

4, 250 -- Since TOTAL ignores dimension, you will get (100 + 200 + 300 + 400)/ 4 = 250 for all dimension values.

Hope this helps you.

Regards,

Jagan.

Re: Re: Difference between Sum(Price) or Total(Price) ??

anbu1984 — Mon, 13 Oct 2014 06:20:18 GMT

I didn't notice Sum in your question.

As Jagan said, Avg(Sum(Price)) is not valid.

Re: Difference between Sum(Price) or Total(Price) ??

nikhilgarg — Mon, 13 Oct 2014 06:25:56 GMT

Hey,

I got your point . But what i want to ask you i just tell you by an example.

Initially when nothing is selected i have month-Year on X-Axis showing price of products. I calculated Avg of them by : Avg(TOTAL Price) which will take sum of all prices displayed and calculate avg.

Now if i select any of the month-year, it will show me prices of products on every date in that month. Now i want to calculate Avg of Prices. Now i use : Avg(TOTAL Price) . Now also it will take the sum of all prices on all dates in that month-year and do avg.

Am i right ??

Re: Difference between Sum(Price) or Total(Price) ??

jagan — Mon, 13 Oct 2014 07:03:29 GMT

Hi,

TOTAL will not ignore selections, if you select one month you will get total average of only that month. If you want to ignore selections and dimensions then use this

Avg({1} TOTAL Price)

{1} - will ignore the selections

Regards,

Jagan.

Re: Difference between Sum(Price) or Total(Price) ??

Mon, 13 Oct 2014 07:29:53 GMT

first of all no TOTAL function is there

instead we can use it as sum( total SAL)

see below image u can understand functionality.