Announcements
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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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.