Nested Set Analysis - New

[ashish_2511]

Dear Community

I know this topic has been discussed a lot but I couldn't find anything relevant to my problem. So here's my problem

I have to write a pivot table  expression like this -

Count(Distinct {<only({<$(vServTarg_ExcludeSelections)>}MTM_COUNTER)=1,
Sum({<BV_DETAIL_LATEST_FLAG={1}, $(vServTarg_ExcludeSelections)>}BV_D_COUNTER)=0,
Sum({<YM_ACTI={$(vMonth0),$(vMonth1),$(vMonth2),$(vMonth3),$(vMonth4),$(vMonth5)}, ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0 >}
CONTACT_ID)

Unfortunately it won't work. Also I cannot write it using 'If' as it won't allow nested aggregation.

Please suggest.

Thanks in advance

Sneh

[jyothish8807]

Hi Sneh,

Can you please explain what are you actually trying to achieve ?

The expression do not make much sense to me.

Br,

KC

[vijetas42]

Please specify your requirement that what you are trying to achieve.

[ashish_2511]

Hi Jyotish

I want to get the Count contactId s. There are 3 conditions that I need to check. The expression is wrong I know but I didn't know how to make it right. so please advise how to make it work. basically it's set analysis within set analysis.

Hope that makes sense.

[ashish_2511]

I want to get the Count contactId s. There are 3 conditions that I need to check. The expression is wrong I know but I didn't know how to make it right. so please advise how to make it work. basically it's set analysis within set analysis

[jonathandienst]

Your syntax is wrong. I suggest that you explain in more detail what you are trying to achieve.

If the expression is not working, why are using variables? Get the expression working in parts and then convert to variables later. Trying to resolve something like this is a lot more complex and it is difficult to make suggestions because you have not provided any information about the variables or your requirements.

[vijetas42]

what are the basic three conditions you want to check in above expression there is so much of confusion

[jonathandienst]

>>There are 3 conditions that I need to check.

Its impossible to make useful suggestions with such a vague comment.

[ashish_2511]

Hi Jonathan

Let me remove the variables, that leaves my expression like this -

Count(Distinct {<only(MTM_COUNTER)=1,
Sum({<BV_DETAIL_LATEST_FLAG={1}>}BV_D_COUNTER)=0,
Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0 >}
CONTACT_ID)

I know this is wrong. Want suggestions to make it right.

I want to achieve - Count(Distinct Contact ID)

There are 3 conditions I want to check in my set analysis -

only(MTM_COUNTER)=1

Sum({<BV_DETAIL_LATEST_FLAG={1}>}BV_D_COUNTER)=0

Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>}ACTIVITY_COUNTER)>0

Help me construct a set analysis expression using this. 'If' is also fine if not set analysis (but if won't allow nested aggregation).

Thanks!

[jonathandienst · Accepted solution]

Much clearer

You will need an Aggr() to do nested aggregations like this and Aggr() needs some dimensions, which are usually the same as your chart dimensions. Lets get it working with an if for now:

Count(Aggr(

  If(Sum({<BV_DETAIL_LATEST_FLAG = {1}>} BV_D_COUNTER) = 0

  And

  Sum({<ACTIVITY_SERVICE_TYPE = {'HT', 'LT'}>} ACTIVITY_COUNTER) > 0

  And

  Only(MTM_COUNTER) = 1,

   Count)

, <dim1>, <dim2>))

<dim1>, <dim2>, .... are the chart dimensions. Not these must be physical fields, not calculated dimensions. Add them as  comma separated list of field names.