Set Expression to get a distinct count when sum is greater than zero with ignoring a selection

[younlim91]

I would like to have the last "count" column to be the result. 
It's a count of distinct dimension1&dimension2 when sum(value 1) group by dimension 1 and dimension 2 is greater than 0, no matter what dimension 3 is selected

Thank you all in advance

[Oleg_Troyansky]

Hi,

This is not quite a Set Analysis issue, per se. Let me give you some pointers to think through:

- First, you are not looking for a distinct count of Dim1&dim2 - that would always return 0 or 1 in your case. In fact, you are looking for a distinct count of Dim1&Dim2&Dim3, with the condition that the sum of Value1 by Dim1&Dim2 is greater than zero.

- In order to solve this in Set Analysis, you'd have to have a single combo field Dim1&Dim2, that you could use for a Set Analysis filter.

- Looking at your example, I think there is a glitch in that logic - the sum(Value1) for A and c is zero, so I'd expect the count to be zero - unless I'm missing something in your requirement

- I'd suggest an approach with the qualifier TOTAL, something like this:

IF ( sum(TOTAL <Dim1, Dim2> Value1) > 0, count (distinct TOTAL <Dim1, Dim2> Dim1 & '|' & Dim2 & '|' & Dim3), 0)

This should get you the desired result, based on your requirements.

Cheers,