Using indirect selection in Set Analysis to get the SUM of amount spent

[AHMB]

Hi,


This expression below correctly gives the number of reactivated customers after a gift campaign targeting dormant customers:

Expressoin 1:

=count(DISTINCT{
(
<[Campaign Customer]={1},ORDER_DATE={"<$(vDateDormantAfter)"}>
-
<[Campaign Customer]={1},ORDER_DATE={">=$(vDateDormantAfter)<$(vCampaignDate)"}>
)
*
<[Campaign Customer]={1},ORDER_DATE={">=$(vCampaignDate)"}>

}PHONE_NUMBER)

Now, I'm trying to get the amount spent in our stores after they have been reactivated using the following expression:

Expression 2:

=Sum(
{<
[Campaign Customer] = {1},
ORDER_DATE = {">=$(vCampaignDate)"},
PHONE_NUMBER = P({
(
<[Campaign Customer]={1},ORDER_DATE={"<$(vDateDormantAfter)"}>
-
<[Campaign Customer]={1},ORDER_DATE={">=$(vDateDormantAfter)<$(vCampaignDate)"}>
)
*
<[Campaign Customer]={1},ORDER_DATE={">=$(vCampaignDate)"}>
} PHONE_NUMBER)
>}
ORDER_WITHOUT_TAX_AMOUNT
)

this gives a number bigger than expected, so I created a Stright Table and added this expression to the dimension:

=Aggr(
    Only({
        (
            <[Campaign Customer]={1}, ORDER_DATE={"<$(vDateDormantAfter)"}>
            - 
            <[Campaign Customer]={1}, ORDER_DATE={">=$(vDateDormantAfter)<$(vCampaignDate)"}>
        )
        * 
        <[Campaign Customer]={1}, ORDER_DATE={">=$(vCampaignDate)"}>
    } PHONE_NUMBER),
    PHONE_NUMBER
)

This returns the correct phone numbers but it also returns "NULL",  and by adding the expression that returns the sum of the amount spent to the table, it turned out the larger than expected amount is coming because of the "Null" value in the dimension.  this can be fixed by excluding null in the table properties but how can I do the same for the expressoin 2 ?

[Bhushan_Mahajan]

@AHMB Try using below one.

=Sum({
(
<[Campaign Customer]={1},ORDER_DATE={"<$(vDateDormantAfter)"}>
-
<[Campaign Customer]={1},ORDER_DATE={">=$(vDateDormantAfter)<$(vCampaignDate)"}>
)
*
<[Campaign Customer]={1},ORDER_DATE={">=$(vCampaignDate)"}>

}Amount)

[AHMB]

Unfortunately this expression does not work

[AHMB · Accepted solution]

I got it working!

=sum(

{<
ORDER_DATE = {">=$(vCampaignDate)"},
PHONE_NUMBER={"=count(DISTINCT{
(
<[Campaign Customer]={1},ORDER_DATE={'<$(vDateDormantAfter)'}>
-
<[Campaign Customer]={1},ORDER_DATE={'>=$(vDateDormantAfter)<$(vCampaignDate)'}>
)
*
<[Campaign Customer]={1},ORDER_DATE={'>=$(vCampaignDate)'}>

}PHONE_NUMBER)>0"}

>}
ORDER_WITHOUT_TAX_AMOUNT)

The entire "count" expression that was in the P() term becomes a search term.  I just needed to convert the inner double quotes to single quotes for it to work.  

what a confusing mess the Set Analysis syntax is .