Hello,
I am wondering if it is possible/how to copy the string measure value from the leaf level to the consolidation level.
I have a date string that shows on the report at the leaf level but not at the consolidated levels of the same report. I am trying to see if it is possible and how to copy the value to the consolidated level. The leaf level consolidations and the consolidations are in 2 separate hierarchies within the same dimension and the date is related to the status of an asset. So whether it is current month or current quarter the date would always be the same for the asset.
Asset number 1 for Jan-22 shows 12/02.2022 - this is as desired
Asset Number 1 for Current Fiscal Quarter and Current Month YTD both show blank would like the same date as above to show at these consolidation levels.
Thanks
Bret
String measure value to consolidation level
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: String measure value to consolidation level
I think you want look at reading from the lowest level rather.
Adding string rules is generally not a good idea as these will fire every time you calculate the view and would impact performance.
You also have the problem of multiple children that could roll up into the C level.
Going with an attribute where you designate an N level as a reference/proxy should get around this.
There may be other solutions but something to ponder so long...
Adding string rules is generally not a good idea as these will fire every time you calculate the view and would impact performance.
You also have the problem of multiple children that could roll up into the C level.
Going with an attribute where you designate an N level as a reference/proxy should get around this.
There may be other solutions but something to ponder so long...
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
Re: String measure value to consolidation level
Could you point me in the direction of some steps or something to google on for this. I am new and my searches are returning nothing useful
Thanks
Thanks
- Elessar
- Community Contributor
- Posts: 340
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: String measure value to consolidation level
Hi,
Primo, beware of several dates being summed up on consolidations
Secundo,
This is much easier with dates, than if it was really a string measure, because you can convert date to number and then to string again.
Here is idea how to do this:
Primo, beware of several dates being summed up on consolidations
Secundo,
This is much easier with dates, than if it was really a string measure, because you can convert date to number and then to string again.
Here is idea how to do this:
- Say we have string "Date" measure where we have values at N-level
- Make a numeric "NDate" measure. "[nDate] = N: DAYNO (DB(cube, .. , Date, ..));", do not forget to feed
- Make reverse rule: "Date = S: IF(consolidated, nDate, continue);" (You cannot use C: for string measures)
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: String measure value to consolidation level
What I was thinking was adding an attribute to the Date dimension like Non-Cumulative Period.
You could then assign a leaf level element to the C levels e.g.
22-Q1 - Jan-22, Feb-22, Mar-22 as you roll months and data is available in the most recent period in the quarter.
22-Q2 - Jun-22
Jan-22 YTD - Jan-22 etc. etc.
You could then reference the comment by first retrieving the N level period from the attribute then doing your DBRW using this.
You could then assign a leaf level element to the C levels e.g.
22-Q1 - Jan-22, Feb-22, Mar-22 as you roll months and data is available in the most recent period in the quarter.
22-Q2 - Jun-22
Jan-22 YTD - Jan-22 etc. etc.
You could then reference the comment by first retrieving the N level period from the attribute then doing your DBRW using this.
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: String measure value to consolidation level
Hi,
You didn't say where the report was being displayed, PAW cube view, PAX?
How is the status of an asset being updated? Is it by the user or is it imported from source system?
If it is updated by the user you could use a TI process to update the consolidated string measures at the same time the user updates the status.
If it is a source system you could incorporate some logic into the data load process which updated the consolidation string values.
Maren
You didn't say where the report was being displayed, PAW cube view, PAX?
How is the status of an asset being updated? Is it by the user or is it imported from source system?
If it is updated by the user you could use a TI process to update the consolidated string measures at the same time the user updates the status.
If it is a source system you could incorporate some logic into the data load process which updated the consolidation string values.
Maren
-
- Posts: 24
- Joined: Mon Jul 19, 2021 2:15 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: 365
Re: String measure value to consolidation level
The data is in Cognos Analytics coming is in data module from a TM1 cube. The report is just displaying data results, there is no user input.
I will look at our data load processes and see what is there
I will look at our data load processes and see what is there
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: String measure value to consolidation level
Given it's for a Cognos Analytics report, if it were me I wouldn't amend anything in TM1 and would amend the query in Cognos Analytics to derive the relevant data. You can use tuple() within an expression to point to which section of the cube to use for the measure containing the date. You'd need some other expressions alongside Tuple to reference the relevant data but it's too late in the day for me to work that one out. I'll have a play next week if no one else comes up with an expression.
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: String measure value to consolidation level
Is the asset element always unique? If so might be worth storing the date as an attribute rather than in the cube (if this an option). That will make it easier to report on