topic Re: Columns Not Adding Correctly in QlikView

Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 19:14:10 GMT

Hello,

I have a quick question that I'm hoping the community can help me with. Attached is a screen shot of a table that I exported to Excel. If you add the columns in the 'Current' column, the total at the bottom of that column is NOT correct. I manually added the 'Should Be' column to show what the total should be. Is there a reason this table is behaving like this?

Any information would be greatly appreciated!

Re: Columns Not Adding Correctly

Nicole-Smith — Thu, 13 Feb 2014 19:37:53 GMT

The problem would be stemming from how you have your expression written, but without knowing anything about your chart, it's impossible to tell what it is exactly.

Or, if you're using a straight table, you can change the total from Expression Total to Sum of Rows:

Re: Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 19:41:07 GMT

Nicole,

I already did what you suggested to get the number right within the Straight Table. Now the issue that I have is that I need that total outside of the Straight Table and they aren't matching. Any ideas?

Re: Columns Not Adding Correctly

maxgro — Thu, 13 Feb 2014 19:41:13 GMT

perhaps you have to uncheck "Suppress when value is null" in dimension tab and/or "Suppress missing" / "Suppress 0 values" in presentation tab

Re: Columns Not Adding Correctly

Nicole-Smith — Thu, 13 Feb 2014 19:44:49 GMT

What do you mean outside of the straight table? In a text box? If that's the case, you'll need to tell me your dimensions and expression.

Re: Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 19:53:12 GMT

Nicole,

That is correct. I am going to need that number for a formula that is in a text box. It looks like all of the columns aren't adding correctly. If you look at the screen shot, column B is just a simple Total Sales column, although I am using Comparative Analysis. Here is the formula:

({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext)

Any ideas?

Thank you so much!

Re: Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 19:53:56 GMT

Massimo,

I don't believe that Null values have anything to do with the issue, but thanks for your input.

Re: Columns Not Adding Correctly

Nicole-Smith — Thu, 13 Feb 2014 19:55:41 GMT

You probably need to use aggr() in the text box and include the dimensions you have on the chart, so you're final formula should look like this:

sum(aggr(sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext), TheFirstDimensionOnYourChart,TheSecondDimensionOnYourChart,etc.))

Re: Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 20:09:02 GMT

Nicole,

I tried using aggr() and I was getting the same results.

Thank you for your suggestions!

Re: Columns Not Adding Correctly

Nicole-Smith — Thu, 13 Feb 2014 20:14:33 GMT

If you can post a .qvw file, I may be able to help you out more...

Re: Columns Not Adding Correctly

mphekin12 — Thu, 13 Feb 2014 21:56:42 GMT

Nicole,

Thanks so much for the offer, but it is a rather large .qvd and it has sensitive data in it.

Re: Columns Not Adding Correctly

simondachstr — Thu, 13 Feb 2014 22:32:39 GMT

Try using your aggr() function including NODISTINCT.

Re: Columns Not Adding Correctly

mphekin12 — Fri, 14 Feb 2014 19:17:43 GMT

I wanted to thank you all for you help! After a lot of QC it appears to be a data issue.