sum() acts strange

[Anonymous]

Hi All, May be am doing a silly mistake, But what might give me this result. What exactly "12/30/1899" mean?? Please find the screenshot.

sum(budget) give 12/30/1899

and avg(budget) give 0.47.

If there are multiple records, sum should be multiples of avg. But how its displaying 12/30/1899.

[Kushal_Chawda]

Go to Number tab of straight table. And set the format as expression default. I think for this expression format is Date which is wrong. Sum is giving result as 0, therefore Date is 12/30/1899 meaning number representation of Date 12/30/1899 is 0

[Anonymous]

Hi Kushal,

The problem here is not date. When i read a value from excel (which shows 0.47), the value is retrieved as '12/3/1899'. Am not sure why this is being caused. And strangely avg() of this value is giving 0.47. 

[Anil_Babu_Samineni]

Would you provide excel, May be you need to chagne that decimal number to Date and then work of metrics. Please provide expected result alsi

[Anonymous]

Sorry Anil, I will not be able to share the excel. 0.47 is the required value.

[vinieme12]

What Kushal is saying is that formatting for the that particular expression is set to Date >> change it to Number in the "Number" Tab

or if that is confusing! then Encapsulate it with Num function!!

Num(Sum(Sales))

[Anonymous]

Vineet, I understood what Kushal is saying. But even if i get the value Num(sum(Sales)) it will give 0. But am expecting 0.47 here.

[vinieme12]

It's giving 0 because we did not specify the number formatting

Try

Num(Sum(Sales),'##.##') or Num#(Sum(Sales),'##.##')

[Anonymous]

Sorry guys. Maybe am not making myself clear. Firstly is there a possibility where 0.47 in excel is read as '12/30/1899' in Qlikview. If yes please let me know.

And then

Whatever might be the number, will it act differently for sum() and Avg() if I have 1 or more records?

[Kushal_Chawda]

Shiva, It will be better if you can share the excel file with sample data and application in which you are facing issue.