topic Re: Using indirect selection in Set Analysis to get the SUM of amount spent in App Development

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

AHMB — Tue, 21 Jan 2025 20:04:57 GMT

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 ?

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

Bhushan_Mahajan — Wed, 22 Jan 2025 05:23:59 GMT

@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)

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

AHMB — Wed, 22 Jan 2025 07:01:41 GMT

Unfortunately this expression does not work

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

AHMB — Wed, 22 Jan 2025 07:09:05 GMT

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 .