on 08-19-2011 3:21 PM
Hi,
I have a requirement to create an evdre input schedule with two dimensions in rows, ACCOUNT and INTCO.
The account dimension should expand using a MEMBERSET of SELF,ALL with the appropriate member set in the CV.
The intco dimension should be selected depending on the account per row with an IF statement (IF(evpro(LEGAL;account;PINTCO)="Y";"INTCO.TOTAL";"I_NONE")
Is this possible? In a non expanding input schedule I can do it, but is it also possible to have the input schedule expand along the account dimension?
Thanks,
Arnold
Hi Arnold,
One possible workaround is to use that formula in a CellKeyRange.
In the evDRE keyrange set the CellKeyRangevalue to =EVRNG(P8)
In cell P8, place your formula
=(IF(evpro(LEGAL;"account";PINTCO)="Y";"INTCO.TOTAL";"I_NONE")
You need to change the "account" word to use an Excel key reference which is dynamic. Test it out and you'll see what I mean.
In my test I had the following in cell P8
=IF(J8="CE0001000","Europe_IN","NA_IN")
Give it a try and you'll see what happens in the evDRE range just to right of the data range.
Note that using CellKeyRanges are a drag on performance so please use them carefully.
You can also use an evGET report to do what you want as well but they perform poorly as well.
Goodluck,
John
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi John,
Let me explain my input schedule in a bit more detail.
My EVDRE in cell A1 references an expansion range with two expansions, both for rows, the first for accounts with a member set of ALL,PARENTAFTER and the second for INTCO with SELF.
This row key range is H17:I118 and the col key range is L15.
Amongst the accounts I see are some with PINTCO = TRUE and some with PINTCO = FALSE. I would like the have the INTCO member, instead of being I_NONE for all accounts, to be INTCO.TOTAL where the account in the same row is PINTCO = TRUE and I_NONE where the account in the same row is PINTCO = FALSE.
So, any formula ro result thereof needs to be in cells I17:I118 and not to the right of the data input area.
I have entered the CellKeyRange as EVRNG(M14) and the formula in M14 is =IF(EVPRO($I$2;H14;"PINTCO")="Y";"INTCO.TOTAL";"I_NONE")
Your solution gives me the correct entry (INTCO.TOTAL or I_NONE) in a column to the right of the data entry area, i.e. column M, however for my input schedule to work it would have to return those values in column I instead.
So, is there a way to get the information from column M to column I?
Thanks,
Arnold
Hi,
I actually thought a lot on this.
1. We cannot keep this formula in the memberset. The account will expand to multiple members. So, the formula wont know, which member to be considered fro EVPRO. So, that wont work.
2. We could have kept only account dimension in the expansion. Just in the adjacent column of account expansion, you could have maintained the formula. Then include both the columns in the keyrange. However, this wont work, because there is expansion happening. If we have expansion, then we need to have both the dimensions in the expansion or none of them.
So, I am afraid this will not work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.