Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarahallen1
Creator II
Creator II

Why is data doubled up here?

I have reduced data down to one example selection state which gives this issue.  So no selections are now needed to replicate this.

I have an expression (for now just in a text box - stripped it right back to what seems to be the root problem):

  • count(Value*[Weight in Fund])

Which gives a count of 2.  I can't see why this is - can't see any multiple rows of non-island data.

Individually, the components of the product are:

  • count(Value)
  • count([Weight in Fund])

which both give a count of 1, as expected.

NB I'm actually wanting to do a sum, but I'm just troubleshooting with a count for now.

Reduced QVW attached.

Any help is much appreciated - this is bubbling up to a weighted average style calc in a pivot table and REALLY confusing users when they cross check with Excel.  I have no idea why it is doubled up.

Thank you!!

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

it is because , you Main table (CompanyAdditional) thru which  Value is Associated to Weight in Funds has 2 rows of Data for the same value

Try

=count(Distinct Value*[Weight in Fund])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

6 Replies
vishsaggi
Champion III
Champion III

May be try this?

= Count(Value)* Count([Weight in Fund])

OR

= Count(DISTINCT (Value * [Weight in Fund]))

vinieme12
Champion III
Champion III

it is because , you Main table (CompanyAdditional) thru which  Value is Associated to Weight in Funds has 2 rows of Data for the same value

Try

=count(Distinct Value*[Weight in Fund])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

May be to Sum, you need this:

=Sum(Aggr(Value*[Weight in Fund], File))

sarahallen1
Creator II
Creator II
Author

Thank you vinieme12‌ , vishsaggi‌, stalwar1‌ .

Vineeth -

I had not spotted that doubled data in CompanyAdditional.  This seems to be the root problem (and explains why, in the non-reduced version of my QVW, I have the problem for some selections and not others).

Vishwarath -

Unfortunately I actually need to do a sum product in a pivot table (expandable to the individual components too).  This count is a simplification of where the problem occurs.

sum({$<Value=-{"",'$(null())'}>}Value*[Weight in Fund])

        /

        sum({$<Value=-{"",'$(null())'}>}[Weight in Fund])

so I can't use count(distinct...) either, as some of the values might be identical for different rows

Sunny -

I will try using aggr in my function like you suggest, over File - thanks

Will update you all once tested!

I will also look upstream in the data processing to find out why I've got duplicates in CompanyAdditional. 

sarahallen1
Creator II
Creator II
Author

Found the reason for the duplicates - a dodgy left join.  Now resolved. 

Thanks all!

vinieme12
Champion III
Champion III

Cheers

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.