Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
mslottje
Creator II
Creator II

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({<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]))

)

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.

Thanks for your help!

Cheers,

Mike

Example1.jpg

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

Check the sample app i posted here,

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

PS: this is a Qlikview app

View solution in original post

6 Replies
kaushiknsolanki

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
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
mslottje
Creator II
Creator II
Author

Thanks for your reply! I tried it, but still got 64 instead of 10 as outcome.

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?

kaushiknsolanki

HI,

Aggregation will be required even though there is 1 dimension.

Share your QVF file

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
mslottje
Creator II
Creator II
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?

kaushiknsolanki

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
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
vinieme12
Champion II
Champion II

Check the sample app i posted here,

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

PS: this is a Qlikview app

View solution in original post