Condition in Expression

[#1]

Hi,

Newbie here... any suggestions on how to remove the Backlog Re-compete line (in red box) from the pivot below? I may just be using the wrong syntax but here is the main logic for the expression for FY17 Total:.

if (Layer -= 'Backlog' and Sub_Layer -= 'Re-compete', Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL) )

TIA for your help!

Carol

[swuehl]

Have a look at

Excluding values in Set Analysis

Identify an appropriate key field you can use within the set analysis, may something like TransactionID, then

Sum({$<TransactionID = e({<(Layer = {'Backlog'}, Sub_Layer = ['Re-compete'}>}), DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

[sunny_talwar]

May be this:

Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}, Layer -= {'Backlog', 'Re-compete'}>} DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

[vikramv · Accepted solution]

Corrected the spelling mistake ''Re-complete'' to "'Re-compete'"

Replace the dimension [Sub Layer]

with

if (Layer = 'Backlog' and [Sub Layer]='Re-compete',null(),[Sub Layer] ) and use supress null on this dimension checked

Keep the expression like...

Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

[#5]

Great idea, I thought it would work but it. I also tried simplifying the expression to like below and it also did not work:

Sum(DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

[#6]

Thanks Sunny, but it did not work.

[vikramv]

Check if my correcting the spelling mistake ''Re-complete'' to 'Re-compete'  ?

[trdandamudi]

Do you think you can share a sample ? So that we can try and give you a better or working solution.

[#9]

Oh, perfect! That worked, thanks!

[#10]

I will look at this too.  Thanks!