topic Re: How we can use both Distinct and sum functions in set analysis in QlikView

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

Thu, 16 Apr 2015 13:59:20 GMT

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)

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

swuehl — Thu, 16 Apr 2015 15:22:30 GMT

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?

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

robert_mika — Thu, 16 Apr 2015 15:33:41 GMT

Try:

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

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

Thu, 16 Apr 2015 15:37:43 GMT

Yeah Sure,

See the below example

here i want the result as

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

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

Thu, 16 Apr 2015 15:42:59 GMT

Robert, Its not working

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

swuehl — Thu, 16 Apr 2015 15:48:40 GMT

Maybe something like

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

But I would rather look into your data model.

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

maksim_senin — Tue, 14 Jul 2015 02:59:42 GMT

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

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

robert_mika — Tue, 14 Jul 2015 03:34:18 GMT

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.

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

john9inno — Tue, 14 Jul 2015 05:14:05 GMT

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)

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

ashwanin — Tue, 14 Jul 2015 05:33:46 GMT

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

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