SUM FUNCTION ISSUE

[Anonymous]

Hello,

I hope someone can help me out.

I have created a model in QV that should allow me to understand if what we load into a converter system is being converted properly by the mapping applied.

For this reason I have loaded the input, the mapping and the output and was triyng to reconcile input and output using QV links beetween tables.

The issue is very basic I think and is that the SUM I uses on input data is correct only when I use the DISTINCT instruction.

Why in a linear table with all possible fileds, do I have to use the distinct instruction in a SUM to get the exact amount I Have in the original field?

Can someone explain to me the basic logic of the sum function?

Thank you in advance

Luigi

[sunny_talwar]

Have you checked if you have multiple lines of data? May be its because of a incorrect join?

[Anonymous]

you were probably right about the multiple lines.

for the moment i have loaded my input in distinct and row magically grow. I am really struggling understing why this is happing. From your questions I thought I was double counting something. any thoughts?

However, my checks now are a little better, but I am not getting the difference between Expression total and sum of rows when using sum().

Could you help?

[parimikittu]

Please load your app here, we will be able to help you with where it is happening and why it is happening.

[hallquist_nate]

Make sure that the keys you are using to link are distinct.  If you don't have distinct keys, Qlik will Associate each instance of the key value, creating extra rows of data.  I see this most when we are creating Composite keys.

Good luck,

Nate

[sunny_talwar]

I am not sure I understand your concern. What exactly is the issue still?

[Anonymous]

That my sum() is giving me two different values either I use Expression total or Sum of rows, but I think that if everything works as it should I would have the same result.

[Anonymous]

your are right, I have non unique keys but some times it is correct that I have them.

could this be the problem? Should I find a way to have them unique in order to get correct summation later?

[hallquist_nate]

If you are getting "additional Rows" in your data, the most common cause that I see is where you have a Many-to-Many join.  It might only be with one or two values in th key field, which can be tough to spot, but it is there.  I would double check your keys and make sure you don't have a many-to-many join in there. 

[Anonymous]

Hi Luigi,

I think that you have two dimensions that are linked with same facts record. You can use Aggr function to resolve it or modify your model for get only one dimension linked for only one group of records.

Regards!