Hi,
I am trying to use a VLOOKUP function in a Websheet, Basically I have a hidden sheet where I have a list of Measures and their Excel Format Code, such as:
Account Balance #,##0_);(#,##0)
Interest Rate 0.0000
.......etc.........
So in my Main sheet, I have an Active Form with dynamic columns ( based on a user selection, different columns show. There is a 2-dimensional cube with 2 dimensions, AccountType and AccountMeasures populated with 0/1, so if a certain Measure is applicable for an Account Type, the value is 1, otherwise 0. Then I have an MDX subset that returns the list of APplicable Measures for the selected AccountType. So then in my ActiveForm Column Header, I use a SUBNM function to retrieve the elements of subset, by applying INDEX=1 for first column, INDEX=2 for second column etc. This is working fine, so the activeform is showing the Measures that are applicable for the selected AccountType.
But because the number or Order of Columns in the active form change, I cannot apply the formatting in the hidden area of active form.
So I am trying to format the ActiveForm Cells by embedding the DBRW formula with the excel TEXT function ( =TEXT ( DBRW ( cubename, el1, el2, .... , elmmeasure ) , MeasureExcelFormat ).
The challenge I am having is, how to pull the Measure Excel Fomat. So i created an attribute on the Measures DImension and then tried to pull the attribute value with DBRA function but it looks like TM1 is not liking that, since I already in my DBRW formula of the Active Form, I am getting the Measure Name through the SUBNM formula, and it looks like TM1 cannot afford an additional layer of nesting TM1 formulas to retrieve the Measure Attribute.
Since the above workaround is not working, I am trying to keep the Measures List with their attributes in a separate hidden sheet and trying to use VLOOKUP function to get the Measure Excel Format. It is working fine in Perspectives, but as soon as I publish it to TM1Web, all the VLOOKUP cells return #N/A
VLOOKUP function is listed as a supported function.
I am using TM1 10.1.1 and Excel 2003
Any idea about how to make this work will be much appreciated
VLOOKUP not working on TM1Web
-
- Community Contributor
- Posts: 152
- Joined: Tue Apr 02, 2013 1:41 pm
- OLAP Product: tm1, cognos bi
- Version: from TM1 9.4 to PA 2.0.9.6
- Excel Version: 2010
- Location: Toronto, ON
VLOOKUP not working on TM1Web
Ardian Alikaj
- mattgoff
- MVP
- Posts: 516
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: VLOOKUP not working on TM1Web
It's a little hard to follow, but you should be able to nest functions as much as you want. In general, when I've come across situations where a cell worked in Excel but not in TM1 Web, I've gone through my formulas with a fine tooth comb and found something that shouldn't have worked anywhere but somehow did in Excel. Think: using a DBRW when I needed a DBR for precedence, over-filtering in the VIEW/TM1RPTVIEW, etc. Don't get hung up on the VLOOKUP, trace back a level or two.
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
- Posts: 78
- Joined: Tue Mar 18, 2014 8:02 am
- OLAP Product: TM1, Cognos Express
- Version: 10.2.2
- Excel Version: 2013
Re: VLOOKUP not working on TM1Web
VLOOKUP only works in the web when you limit the range. Looking up in an entire column does not work.
-
- Posts: 62
- Joined: Mon Aug 15, 2016 8:48 am
- OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
- Version: 10.2.0 FP3
- Excel Version: Excel 2013
- Location: Hamburg, DE, EU
- Contact:
Re: VLOOKUP not working on TM1Web
For me it worked even not with a restricted (named) range.
TM1 10.2.0 FP3, and, indeed, it is the german version of Excel 2013 I use for development.
But on the server is installed english version of Excel 2010.
Could the different versions of Excel cause additional malfunctions?
TM1 10.2.0 FP3, and, indeed, it is the german version of Excel 2013 I use for development.
But on the server is installed english version of Excel 2010.
Could the different versions of Excel cause additional malfunctions?
-----------------------------------
Best regards,
Stefan
Best regards,
Stefan