Issues formatting formula in Set analysis expression

[#1]

Hello.  I searched for a solution to my issue but was unable to find an existing thread.

The following formula works properly to display sale1 in this expression.

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>} SALES1),

sum(SALES1)

)

My problem is that I would actually like to replace 'SALES1' with a more complex formula listed here:

sum(if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2))

I've tried to simply swap this more complex formula in places of 'SALES1' but it breaks my expression and results in blanks fields. 

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>} [

sum(if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2))]),

sum(SALES1)

)

Your help would be greatly appreciated.

[lironbaram]

hi try something in the line of:

if (

NETWORKID = 9415.1,

sum( {$<AMP_DP_NETWORKID -= {9003.1}>}

if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>}if (InDay (statistics.date, Today(), -2), SALES2)),

sum( if (InDay (statistics.date, Today(), -2), SALES1)) +

sum(if (InDay (statistics.date, Today(), -2), SALES2)),

)

[jonathandienst]

Hi

I think this should do the trick:

If(NETWORKID = 9415.1,

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES2)),

sum(SALES1)

)

Hope that helps

Jonathan

[#4]

Thanks for the quick response and sorry the delay in mine - was traveling.   This works, thank you. 

It seems that I have to include NETWORKID as a dimension in my table in order for this expression to work though.   Is there any way around that?

I'd love to calculate the sales based on this expression but roll them up by a different dimension in a summary table.

Thanks again.

[jonathandienst]

I assumed that NETWORKID was something you had selected. If you want to include this on a table/chart with other dimensions, you may be able to use:

Sum(Aggr(

If(NETWORKID = 9415.1,

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES1)) +

sum({$<AMP_DP_NETWORKID -= {9003.1}>} if (InDay (statistics.date, Today(), -2), SALES2)),

sum(SALES1)

, Dim1, Dim2, Dim3))

Where Dim1 - Dim3 are the dimensions on your chart (change to the correct number and names of the chart dimensions). Whether this works or not depends though on the structure of your model and where these fields are located.

Jonathan

Edit: fixed parentheses

[CELAMBARASAN]

This might helps

Sum({$<NETWORKID ={9415.1},AMP_DP_NETWORKID -= {9003.1}, statistics.date={"=InDay(statistics.date, Today(), -2)"}>} SALES1) +

Sum({$<NETWORKID ={9415.1},AMP_DP_NETWORKID -= {9003.1}, statistics.date={"=InDay(statistics.date, Today(), -2)"}>}  SALES2) +

Sum({$<NETWORKID -={9415.1}>} SALES1)

[#7]

Thanks again for your help.  I do have NETWORKID selected as a dimension in another chart on another tab so your original solution definitely worked for that. Call that object 1.

This is more of a part II to my original question.   Here I want to basically pull the total line from object 1 into a summary tab.    I first tried to copy object 1 into the summary tab and then remove NETWORKID and add COUNTRY in the dimension list.   This is making the 'if' statement = false (I presume because the argument references a NETWORKID which is now no longer in the object).

Anyway, I tried your solution below but unfortunately it didn't work. As you mentioned it must have something to do with my data model.

Any other suggestions?

[#8]

Thanks for your help.   Using this results in "Error in set modifier ad hoc element list".