1 2 Previous Next 16 Replies Latest reply on Aug 27, 2018 6:17 PM by bhargavi chittibomma

# Exclude dimension dynamically using calculated field

Hi All,

Need some help in dynamically excluding the  dimension value

if [Revenue Month]= "2018" and [Ebit Tree Level 3]="CardHolderMonthlyFees" and

[Business Calssification Category]="Prepaid Open Loop" then [Revn Amt]

ELSEIF [Revenue Month]> "2018" and [Ebit Tree Level 3]="CardHolderMonthlyFees"  or [Ebit Tree Level 3]="Pin Fees" or

[Ebit Tree Level 3]= "Signature Fees" and

[Business Calssification Category]="Prepaid Open Loop" then [Revn Amt]

else null end

not sure what I did wrong here...

All I need is.. in 2018 I have to consider only "cardholdermonthlyfees" and in 2019 i have to include all  "cardholdermonthlyfees","Signature Fees","Pin Fees"  thats the logic I am trying to implement here..

Any immediate help on this would be appreciated

Thanks

Bhargavi

• ###### 1. Re: Exclude dimension dynamically using calculated field

What other values do you have [Ebit Tree Level 3] that you didn't list in the logic for the second half of your calc?

If you only care about "CardHolderMonthlyFees" for 2018, and you want to grab ALL [Ebit Tree Level 3] values for other years, than you can do this:

if [Revenue Month]= "2018"

then if [Ebit Tree Level 3]="CardHolderMonthlyFees"

then [Revn Amt] END

ELSE

[Revn Amt]

END

Note: this will get  [Revn Amt] for all [Ebit Tree Level 3] values in 2017 too.

• ###### 2. Re: Exclude dimension dynamically using calculated field

The abpve logic seems like not working to me

in the snippet above you can actually seethe data for all 3 but our expectation is to show only cardholder monthly fees

• ###### 3. Re: Exclude dimension dynamically using calculated field

if [Revenue Month]= "2018" and [Business Calssification Category]="Prepaid Open Loop"

then if [Ebit Tree Level 3]="CardHolderMonthlyFees"

then [Revn Amt] END

ELSE

[Revn Amt]

END

thats the logic which i have implemented

1 of 1 people found this helpful
• ###### 4. Re: Exclude dimension dynamically using calculated field

Where is Revenue Month in all that?

I'll need a workbook to help here.  At this point I can only guess at all the things you are doing on that sheet (and guess at what you really need to do with this calc.)

• ###### 5. Re: Exclude dimension dynamically using calculated field

Revenue month is the date field...unfortunately i cannot send you the workbook because of its confidentiality

• ###### 7. Re: Exclude dimension dynamically using calculated field

Oh, I see Revenue Month in there now.

You can't use EQUAL in your calc.  You have to use CONTAINS([Revenue Month], "2018")

• ###### 8. Re: Exclude dimension dynamically using calculated field

Also, you'll need to have the [Business Classification...] logic in the second half of that calc.

if CONTAINS([Revenue Month], "2018") and [Business Calssification Category]="Prepaid Open Loop"

then if [Ebit Tree Level 3]="CardHolderMonthlyFees"

then [Revn Amt] END

ELSEIF  [Business Calssification Category]="Prepaid Open Loop"

[Revn Amt]

END

• ###### 9. Re: Exclude dimension dynamically using calculated field

if CONTAINS([Revenue Month], "2018") and [Business Calssification Category]="Prepaid Open Loop"

then if [Ebit Tree Level 3]="CardHolderMonthlyFees"

then [Revn Amt] END

ELSEIF  [Business Calssification Category]="Prepaid Open Loop" then

[Revn Amt]

END

gives the below result

• ###### 10. Re: Exclude dimension dynamically using calculated field

I need a workbook.

• ###### 11. Re: Exclude dimension dynamically using calculated field

I have attached workbook.. hope this helps

• ###### 12. Re: Exclude dimension dynamically using calculated field

See attached.  I didn't change anything.  I just put stuff on the sheet, and it's doing what I expect it to be doing.

I don't see anything in the sample workbook about Actuals and Quota (like you show in your  screen shot), but they don't really come into play with what we're doing anyway.

If you added any 2019 data for "Acquiring" under "Prepaid Open Loop", you would see that all the values would be carried to [Ebit Tree level].

See attached.

• ###### 13. Re: Exclude dimension dynamically using calculated field

but not sure I am not seeing the result what you are seeing...dont know what went wrong

• ###### 14. Re: Exclude dimension dynamically using calculated field

There could be a simple typo in your actual workbook.  I really can't say.

In the attached I made a copy of a subset of your sample data (excel file attached), and hacked up some data values. Changed two rows to have 2019 data in there, and added a bogus Level 3 value to show you that the calc in the example is doing what we want.

1 2 Previous Next