topic Re: sum() acts strange in QlikView

sum() acts strange

Anonymous — Sun, 02 Apr 2017 07:55:22 GMT

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.

Re: sum() acts strange

Kushal_Chawda — Sun, 02 Apr 2017 08:13:38 GMT

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

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 08:18:19 GMT

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.

Re: sum() acts strange

Anil_Babu_Samineni — Sun, 02 Apr 2017 08:24:29 GMT

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

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 09:18:13 GMT

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

Re: sum() acts strange

vinieme12 — Sun, 02 Apr 2017 09:32:14 GMT

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))

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 09:59:20 GMT

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.

Re: sum() acts strange

vinieme12 — Sun, 02 Apr 2017 10:04:27 GMT

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

Try

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

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 10:20:43 GMT

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?

Re: sum() acts strange

Kushal_Chawda — Sun, 02 Apr 2017 10:37:33 GMT

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

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 10:41:10 GMT

Kushal, Am not able to reproduce the situation. It's not happening now. I just want to know what might have happened.:(

Re: sum() acts strange

Anil_Babu_Samineni — Sun, 02 Apr 2017 10:41:30 GMT

Your original question is not clear, I would highly asking you to create sample data atleast 5 rows and then tell us your expected result in UI. So then we can help you

Re: sum() acts strange

sunny_talwar — Sun, 02 Apr 2017 10:55:59 GMT

Can you at least share some screenshots? May be while loading your data you need to use Num(Sales, '#,##0.00) as Sales

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 11:00:53 GMT

Sunny, the screen shot i have attached in the question is the only one i have. Am not getting the issue anymore.

The changes i did are removing a sync table. And with that the issue is gone. Am not sure what exactly happened. Thinking there might be some thing i did it wrong which converted by decimal number to a invalid date format value.

Thank you guys for your time.

Re: sum() acts strange

sunny_talwar — Sun, 02 Apr 2017 11:04:34 GMT

I am still not sure what exactly are you saying here

Here you mentioned that the issue was while retrieving the data from Excel. But in your screenshot you are showing a straight or pivot table with the incorrect formatting?

Re: sum() acts strange

Kushal_Chawda — Sun, 02 Apr 2017 11:07:12 GMT

Sunny, that is why I suggested to check the Straight table Number format. May there was a format of date

Re: sum() acts strange

sunny_talwar — Sun, 02 Apr 2017 11:09:01 GMT

It seems that the formatting might have been set as date on the number's tab, is that what you are thinking happened also?

Re: sum() acts strange

Kushal_Chawda — Sun, 02 Apr 2017 11:10:58 GMT

exactly bhai.. aapun ye hi bolna chahta

Re: sum() acts strange

Anonymous — Sun, 02 Apr 2017 11:13:16 GMT

No guys, The format was Expression default. I checked that. Leaving the straight table aside, when I preview the data with a Ctrl+ T I was looking at 12/30/1899. as value.

Re: sum() acts strange

sunny_talwar — Sun, 02 Apr 2017 11:20:17 GMT

That's exactly what I meant... if you see 12/30/1899 in data model, then you could have tried Num(Sales) to see if that fixed the issue... but since its already resolved, you should be all good....

Please close this thread if you have got what you wanted. You can mark assumed answer or your own response as correct

Best,

Sunny