Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Sneh,
Can you please explain what are you actually trying to achieve ?
The expression do not make much sense to me.
Br,
KC
Please specify your requirement that what you are trying to achieve.
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.
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
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.
what are the basic three conditions you want to check in above expression there is so much of confusion
>>There are 3 conditions that I need to check.
Its impossible to make useful suggestions with such a vague comment.
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!
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.