Hi All,
I have a rule in tm1 on my time dimension member
like ['YTD Growth'] = IF(['YTD Change'] <> 0,(['YTD Change']/['Prior YTD Grouped']),0);
How can I achieve the same in my transformer.
I am able to see only calculated measure having functions.
I am using my tm1 time dimensions flat file as a source for building time dimension in transformer.
Any help is appreciated.
You do not have to define these relative time categories in Transformer because Transformer creates these automatically. When you create a time dimension in Transformer, it will look for the current date depending on which data source is defined as the source to
identify the current date and then it will generate the YTD. PYTD, YTD Change and YTD Growth automatically.
If I dont use transformers time dimension , I have it built manually from flat file, then where do I wrtie the rule for YTD Growth in Transformers.
Please advise.
Thanks
Why would you want to use your flat file's manual rules? Why not just provide the date to Transformer and let it build the Time dimension? It also lets you create custom time categories. If you are trying to replicate a TM1 cube in Transformer then there are a few things which will not be a straight feed but rather done differently than how it is done in TM1 to come up with the correct solution.
Hi CognosTechie,
Sorry for confusion , the time dimension is built automatically by transformer..
My only question is for YTD Growth I am doing a customized calculation.
Like my formula changes from what tansformer does.
So what I would do is go the time dimension categories and delete the ytd growth member and drag a new member and say calculated categories and here I am not able to use if statement, I can just add or subtract members.
Attached the screenshot, Please Advise.
I appreciate your suggestions.
Thanks,
Manu
Is there a reason to use the If statement in this case? Your If statement is checking if the YTD change has a value other than 0 (based on your original post). If that's the only thing you want to check then there is no reason to check, just leave it the way Transformer creates it. It doesn't hurt to calculate YTD Growth regardless of whether there is a difference or not between the Current year and last year. Are you trying to do it that way only because it was in TM1 like that or because it is actually required ?
No, if prior value is zero then in cognos it would show up as /0.So I wanted my own formula here. I also wanted to know if we will be able to use if else in calculated category so we can have our own rules with in transformer.
Thanks,
Manu
I don't think you can put an if statement in a category. By the way, TM1 is ROLAP whereas Transformer is MOLAP. 'If' statement is a relational statement and hence can only be used in a SQL, not in a category in Transformer.
You could create a calculated column in your data sources using IF in the expression, then use that column as a category. IF is not a relational thing in this context, it's a Cognos function.
How would you make that calculation effective in the category for YTD Growth or even YTD Change ? Those categories are generated by Transformer and it populates the Time dimension by itself. How would it use that calculation which you define in the data source?
Sorry for any confusion.
I am not familiar with TM1 or using auto-generated time dims in Transformer (we use custom table-driven time dims). I only wanted to clarify that IF was not a database function, and that it was possible to have calculated members/measures in Transformer cubes.
Very true. IF is a relational function as well as a Cognos function to capture the rule/logic which at the time of execution gets converted to an expression depending on what is required for the scenario. What I meant to say was that IF cannot be used in a time based category in Transformer which is what the OP was asking about. It can be used in calculated measures in Transformer and datasources for Transformer. It is also possible to have calculated relative time categories in Transformer but till date I haven't seen any way to use IF statement for those categories.