# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Set analysis if sum > 0 then count distinct

Hi experts,

I want to count the amount of new customers that have ordered in Q2 current year.

I'm using the following script:

 if(SUM({}[Nett Amount new EUR]) > 1, count(distinct{<[Bill-to-Customer Territory Code]={'A','B'}, CreationQuarter={'Q2'},CreationYear={\$(=Year(Today()))}>}([Bill-to-Customer Number])) )

When I use this script in a bar chart I get 64 as outcome.

This also happens when I use this script in a table with 'auto' as totals function.

When I change the totals function from auto to sum, I get 10, which is the correct number.

Any idea how I should change my script to get the same outcome as a table with the totals function on sum instead of auto?

Below you'll find a screenshot of the totals function in a table, to help you understand.

Cheers,

Mike

1 Solution

Accepted Solutions
Champion III

Check the sample app i posted here,

Re: Nested Set Analysis - Customer bought in both time periods

PS: this is a Qlikview app

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

Hi,

Try this.

Sum(Aggr(if(SUM({<Year={'2017'}, Quarter={'Q2'},[Bill-to-Customer Territory Code]={'A','B'}>}[Nett Amount new EUR]) > 1,

count(distinct{<[Bill-to-Customer Territory Code]={'A','B'},

CreationQuarter={'Q2'},CreationYear={\$(=Year(Today()))}>}([Bill-to-Customer Number]))

),Dim1,Dim2))

Replace Dim1 and Dim2 with the actual dimension you have in table.

Regards,

Kaushik Solanki

Regards,
Kaushik
Anonymous
Not applicable
Author

I'm using 2 measures and therefore only 1 dimension..

My dimension is the Bill-to-Customer Territory Code.

The Aggr function is then not useful anymore isn't it?

HI,

Aggregation will be required even though there is 1 dimension.

Regards,

Kaushik Solanki

Regards,
Kaushik
Anonymous
Not applicable
Author

Ah okay! I would love to share the QVF, but I'm not allowed as the QVF contains confidential data..

Any other ideas how I could help you any further with understanding my problem?

Hi,

You can create a sample data which can help us to understand the issue and then share with us.

Regards,

Kaushik Solanki

Regards,
Kaushik