6 Replies Latest reply: Jul 1, 2017 5:50 AM by Vineeth Pujari RSS

    Set analysis if sum > 0 then count distinct

    Mike Slottje

      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