Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

cheburashka
Contributor III

The sum of a field over a dimension does not give the correct output.

Hello,

I have a straight table where I have one expression (a sum) and multiple dimensions. The sum does not give the answer it should give.

Is this a bug in Qlikview?

Please take a look at the attached qvw for a more detailed description of the question.

_Koen

1 Solution

Accepted Solutions

Re: The sum of a field over a dimension does not give the correct output.

Looks like you have duplicate records. Add an expression count([# of hours]) to the same chart. You'll see a count of two for the second and third last lines. That means you have two records with the same values. Qlikview shows only one row for duplicate records.


talk is cheap, supply exceeds demand
7 Replies

Re: The sum of a field over a dimension does not give the correct output.

Looks like you have duplicate records. Add an expression count([# of hours]) to the same chart. You'll see a count of two for the second and third last lines. That means you have two records with the same values. Qlikview shows only one row for duplicate records.


talk is cheap, supply exceeds demand
cheburashka
Contributor III

Re: The sum of a field over a dimension does not give the correct output.

Oke I tried that.

But why is there only one record visible in the "All Data" table box?

I guess you mean that "Qlikview shows only one row for duplicated records" ?

In the table viewer > preview I can indeed see that there are duplicated records.

Why are there duplicated records? Can I remove them with an extra line in the script?

Thx for your advice.

_Koen

Re: The sum of a field over a dimension does not give the correct output.

My guess would be that the duplicates are caused by one of the joins somewhere or perhaps by the intervalmatch. You could do a load distinct on the resulting Master data table so you have a deduplicated table. But I think you should look at the joins first. There may be other problems hidden in there. Better ot make sure that the loads/joins are correct imho.


talk is cheap, supply exceeds demand
Employee
Employee

Re: The sum of a field over a dimension does not give the correct output.

I agree with Gysbert Wassenaar - check that the joins are correct.

However, I would take it one step further: You should question the joins altogether. They might be totally unnecessary, and should then be removed.

Read more on To Join or not to Join.

HIC

cheburashka
Contributor III

Re: The sum of a field over a dimension does not give the correct output.

I went for the easy solution.

I wrote the following line to remove the duplicated record lines.

Load distinct * Resident [Master data];

I have no idea  what I should look for when I check the joins and intervalmatch.

It is the first join I ever did, and I used the script Gysbert Wassenaar advised me in a earlier question.

For now it is working .

Thx for your advice

_Koen

cheburashka
Contributor III

Re: The sum of a field over a dimension does not give the correct output.

I've read your blogpost. In this Qvw the JOIN was used because it was part of the solution for my question on Re: How to built a report table depending on whether two periods overlap?. I'm considering of not using a JOIN statement. But I need a working intervalmatch though.


Is it possible to do a interval match between a date of one table and a interval which is part of a different table?

How will the date and the interval be linked if you dont JOIN the 2 tables?

_Koen

Employee
Employee

Re: The sum of a field over a dimension does not give the correct output.

The intervalMatch creates a table with exactly three fields: Date, IntervalStart and IntervalEnd. And with these fields it becomes a bridge table that links the date table and the interval table.

An alternative to intervalmatch is a while loop:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

Community Browser