Quick question about sum function

[#1]

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.

[jonathandienst]

Hi

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

[#3]

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

[#4]

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

[jonathandienst]

Hi

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

Regards

Jonathan

[jonathandienst]

Hi

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

[Jason_Michaelides]

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

[#8]

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.

[jonathandienst]

Hi

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

[#10]

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

[jonathandienst]

Hi

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