topic Quick question about sum function in QlikView

Quick question about sum function

Mon, 08 Aug 2011 08:21:48 GMT

Good morning, I am trying to sum all the values that match one specific condition but I can sort it out. If first I show all the values I want to sum it is correct:

EventId RISK REVENUE AMOUNT 1

76672 $25,131.50

176674 $25,131.50

176671 $31,600.00

176673 $31,600.00

I used the dimension EventId and this expression:

= IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

(ProjectedRevenue)

)

but when I use the sum function it prints a result doesnt make any sense cos it doesnt show the total that should be $113.463:

EventId RISK REVENUE AMOUNT

176672 $1,759,205.00

176671 $2,212,000.00

176674 $3,518,410.00

176673 $4,424,000.00

I used the dimension EventId and this expression (the same than before but with the sum function at the begining:

= sum(IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

(ProjectedRevenue)

)

I didnt selected the option "Full accumulation" in Expressions tab.

Could you please tell me what I am doing wrong?

Thanks a lot in advance.

Re: Quick question about sum function

martin59 — Mon, 08 Aug 2011 09:46:23 GMT

Hi,

If you use a straight table, you have to do the "sum of rows" in expression tab

Hope that helps you

Martin

Re: Quick question about sum function

Mon, 08 Aug 2011 09:51:49 GMT

Thank you for your reply. The thing is that I want to show only the total sum of those values, not the whole table.

Regards.

Quick question about sum function

martin59 — Mon, 08 Aug 2011 09:54:44 GMT

Ok,

You have to remove your dimension "Event ID"

Is that you want ?

Quick question about sum function

Mon, 08 Aug 2011 10:02:18 GMT

Thanks again for your quick answer. If I do that, using a straight table I get the value 11.913.615 (that is wrong cos it should be $113.463). That number seems to be the sum of all of the other wrong values I get when I use the sum function:

EventId RISK REVENUE AMOUNT

176672 $1,759,205.00

176671 $2,212,000.00

176674 $3,518,410.00

176673 $4,424,000.00

Regards.

Quick question about sum function

Wed, 10 Aug 2011 15:22:56 GMT

So, it is still wrong cos the total should be $113.463 , not 11.913.615

Could you please help me?

Quick question about sum function

jonathandienst — Wed, 10 Aug 2011 18:41:28 GMT

I suggest that you post your model or a sample thereof.

Jonathan

Quick question about sum function

its_anandrjs — Wed, 10 Aug 2011 18:45:26 GMT

Hi Yomarcos2,

I think you have to use Sum(Total Column1) like this you get total value.

Regards,

Anand

Quick question about sum function

Thu, 11 Aug 2011 09:54:43 GMT

Hi its.anandrjs, with Column1 you mean the EventId? If so, I need to sum "Risk revenue amount", not EventId.

I tried to do sum(total RiskRevenueAmount) but it didnt work either, i got the message "No data to display".

Regards.

Quick question about sum function

Thu, 11 Aug 2011 10:03:06 GMT

Hi Jonathan Dienst , this is my DB model, basically I look every row identified by "EventId", and if the fields in that row meet my conditions I want to get the ProjectedRevenue value (in table TblERPData) and add it up to the one I got previously to get the total amount, I mean to sum up all the ProjectedRevenue's that meet my conditions.

THANKS A LOT!

Quick question about sum function

jonathandienst — Sun, 14 Aug 2011 20:30:16 GMT

I actually meant to post your QVW file, or at least a sample from it. But looking at your model diagram, I am concerned abou the numbr of synthetic keys that are being generated. While they are not inherently "bad", they often indicate flaws in the model (ie unintended links, with hard to interpret consequences)). If it were my model, I would review the design carefully to ensure that this is correct.

Unfortunately, the diagram does not help me get much closer to the problem in your original post.

Regards

Jonathan

Quick question about sum function

Mon, 15 Aug 2011 10:40:21 GMT

Hi Jonathan, thanks for your reply. The thing is that I can't upload the whole file cos there are a lot of confidential data.

Anyway, what I dont understand is that if I show all the values I want to sum up, it is OK (as you can see as follows) but if I use the function sum just to see the grand total, the result is not correct.

EventId RISK REVENUE AMOUNT 1

76672 $25,131.50

176674 $25,131.50

176671 $31,600.00

176673 $31,600.00

If I use the sum function I get the value 11.913.615 which is really really far away from the correct result $113.463

Quick question about sum function

Wed, 17 Aug 2011 10:09:23 GMT

Hi Jonathan, I reduced the data of my file so I can post it here, it is the file in My Documents called "R&B Tracker dashboard_v6env.qvw"

I realized that the result I obtain it comes from multiplying the Revenue value in each EventId by 70 or 140, but it shouldnt be like that (you can see this on "Table1 Correct"). I dont know why it counts 70 times the same EventId cos in my files that EventId it is only once or maybe 3 times if I count all the tables, but not 70 times. It should multitply the Revenue value in each row by 1 to get the correct result.

Can you have a look to see whats wrong?

Thanks a lot !!

Quick question about sum function

jonathandienst — Sun, 21 Aug 2011 09:42:48 GMT

For some reason I am getting an access denied message when I try to open your document.

Regards

Jonathan

Quick question about sum function

jonathandienst — Sun, 21 Aug 2011 09:49:01 GMT

yomarcos2 wrote:
I realized that the result I obtain it comes from multiplying the Revenue value in each EventId by 70 or 140, but it shouldnt be like

This is could be due to the composite key, but it could also be an error in your load script. Does any table have 70 rows during or after the load?

You may need a LOAD Distinct one ot more tables in your script. And check the fields in your joins to ensure that you are not performing a cross join by accident.

Regards

Jonathan

Quick question about sum function

Jason_Michaelides — Sun, 21 Aug 2011 12:02:05 GMT

Sorry to jump in but like Jonathan I think you really need to review your data model first. Far too many synthetic keys in my opinion and proabably too many tables as well - try joining some into eachother or use ApplyMap() to reduce your joins and simplify the whole thing. Then it may be easier to help you answer the specific question.

Jason

Quick question about sum function

Mon, 22 Aug 2011 13:18:52 GMT

Hi people, I dont know why the .qvw disappeared from My documents, and now I cant upload files anymore... Where can I upload the file so you can all see the document?

I dont have any table with 70 rows of the same EventId.

I didnt use any JOIN cos Im just extracting the tables from an Excel and Acess document.

Thanks all of you.

Re: Quick question about sum function

jonathandienst — Mon, 22 Aug 2011 19:06:47 GMT

yomarcos2 wrote:
Hi people, I dont know why the .qvw disappeared from My documents, and now I cant upload files anymore... Where can I upload the file so you can all see the document?

Just attach it to your post. Cick "Use advanced editor' in the top right corner of the Add reply box and then click "Attach Files".

Regards

Jonathan

Re: Quick question about sum function

Tue, 23 Aug 2011 07:54:02 GMT

Thanks a lot Jonathan. Here you can find attached the file.

Re: Quick question about sum function

jonathandienst — Tue, 23 Aug 2011 18:54:13 GMT

After looking at your model here are my comments:

In the context of your model design, the "wrong" answer is correct (11,913,615). This is because the first two eventIDs have 70 replicates - 70x the Projected Revenue your display because you used ProjectedRevenue instead of Sum(ProjectedRevenue) in your expression. The next two eventIDs have 140 replicates. This could be due to the implicit joins you create by loading the way you have, or it could be a problem with your source data.
ProjectedRevenue is on the composite keys Syn11, Syn8 and Syn7. Having metrics on keys is asking for trouble! It is beyond me to untangle this.
Perhaps you need to start with a much simpler model and build it step by step.You cannot (or rather should not) build a qilikviw model by dumping a whole lot of tables to a model and hope that it will work, The model needs to be designed to fulfill a purpose, with consideration of what fact tables, metrics and dimensions are needed to achieve the design objectives.

Here are some suggestions:

Decide on the dimensions (eg date, project manager, business unit (hierarchy), etc)
Consider each table in your source and decide which are needed most,and start with those.
Organise your tables into a star or snowflake structure with the facts (metrics) in the fact tableat the central node, and the dimensions linked to the fact table
Seperate the project level data, the event level data, task level data and the business unit heirarchy level data. At the moment its all mixed up.
More generally, separate the dimensions from the facts
Simplify the structure by joining tables. For example, all the Syn9 tables should be joined into a single table like this:

Changes:
SQL SELECT *
FROM TblChangedEventDates;
Left Join (Changes) 
LOAD *;
SQL SELECT *
FROM TblChBillTodayEventsRecord;
Left Join (Changes) 
LOAD *;
SQL SELECT *
FROM TblChRevTodayEventsRecord;
etc

Then try to merge the New and ERPData tables together

If you are not sure how to do this, consider hiring a QV professional consultant to assist in getting the project going.

Good luck

Jonathan