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: 
Not applicable

How we can use both Distinct and sum functions in set analysis

How we can use both Distinct and sum functions in set analysis?

See the below expression where i need to take the distinct values of the field Number before taking the Sum of Amount

=sum({<

        Distinct(Number),

        AAAAA={'C'}

      

  

>}Amount)

9 Replies
swuehl
MVP
MVP

Sorry, I don't understand what you mean with " i need to take the distinct values of the field Number before taking the Sum".

Could you explain this with some lines of sample data and your expected outcome?

robert_mika
Master III
Master III

Try:

sum({$<Number= {'=COUNT(DISTINCT Number)'},AAAAA={'C'}>} Amount)

Not applicable
Author

Yeah Sure,

See the below example

Test1.JPG

here i want the result as

Test2.JPG

here you can see like we are taking the distinct of Test1 and after that i want to sum the field Test3.

Not applicable
Author

Robert, Its not working

swuehl
MVP
MVP

Maybe something like

=sum( aggr( only(Test3), Test1, Test2))

But I would rather look into your data model.

maksim_senin
Partner - Creator III
Partner - Creator III

Hi,

My suggestion is to move the calculation into the load script. Maybe you'll also need to calc count of such records, sum of some fields, etc. But you'll definitely avoid extra calculations in run-time.

Best regards,

Maxim

robert_mika
Master III
Master III

Did you try Stefan's solution or Maxim's suggestion?

If it still does not work and you this as Set Analysis - post your data+error+desired result.

john9inno
Creator
Creator

I simply added TEST1 and TEST 2 as dimensions into straight table and in Expression i used below and I could get what you described.

Sum(distinct Test3)

ashwanin
Specialist
Specialist

I believe the Swuehl 's answer is perfect for your requirement.

I tried it on your given data . please see the qvw.