Set analysis if sum > 0 then count distinct

[Anonymous]

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

[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

[Anonymous]

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

[Anonymous]

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

[vinieme12 · Accepted solution]

Check the sample app i posted here,

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

PS: this is a Qlikview app