CELLPUTN
-
- Posts: 58
- Joined: Thu Jul 03, 2014 1:14 pm
- OLAP Product: cognos
- Version: 10.2.2
- Excel Version: 2010
CELLPUTN
Hello
I have been able to post 1 against two dates, Jan-15, Dec-34 via ti, but how do I put 1 against any dates between Jan-15 and dec-34 as they are strings, and im sure you cant do <= and => between strings.
Can anyone offer any advise to what I could do to get round this issue?
Thanks in advanced
Mithun
I have been able to post 1 against two dates, Jan-15, Dec-34 via ti, but how do I put 1 against any dates between Jan-15 and dec-34 as they are strings, and im sure you cant do <= and => between strings.
Can anyone offer any advise to what I could do to get round this issue?
Thanks in advanced
Mithun
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
Dear friend,
This is not an enhancement, so you are posting in the wrong subforum.
You registered to this forum more than 3 years ago, so this should not be new to you.
I assume this topic will be moved shortly.
Other than this, use a normal WHILE() End loop to loop over dates. Not knowing the design of your cubes and dimensions,
this is about the best answer you can hope to receive.
This is not an enhancement, so you are posting in the wrong subforum.
You registered to this forum more than 3 years ago, so this should not be new to you.
I assume this topic will be moved shortly.
True, but you can use @<= and @=> when you compare textual values - given that the strings are built up such that alphanumeric comparisons are correct.Mithun.Mistry1103 wrote:and im sure you cant do <= and => between strings.
Other than this, use a normal WHILE() End loop to loop over dates. Not knowing the design of your cubes and dimensions,
this is about the best answer you can hope to receive.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 58
- Joined: Thu Jul 03, 2014 1:14 pm
- OLAP Product: cognos
- Version: 10.2.2
- Excel Version: 2010
Re: CELLPUTN
Hello Wim
Apologies for that. I will ensure that I dont do that again
In regards to your response to the problem, thank you for the suggestion. I thought of doing a while loop, the only thing that discouraged mewas the following details:
My script is:
It would not recognise @=> and secondly, the question comes to mind is, how do i say, stop putting 1 into intersections after vFinishDate and if the vstartdate is '', just start putting 1 from the first level 0 node in the vMonthdim.
In terms of the cube and dimension designs, there are 6 common dimensions, the 1 dimension that is different, for cube 1, its a month dim, with Jan-15 etc periods as 0 level nodes and in cube 2, the startdate and finishdate is string values within the nodes intersections.
I will continue to work on it.
Thank you once again sir.
Apologies for that. I will ensure that I dont do that again
In regards to your response to the problem, thank you for the suggestion. I thought of doing a while loop, the only thing that discouraged mewas the following details:
My script is:
Code: Select all
#vstartdate=cellgets();
#vfinishdate=cellgets();
vstartdate = cellgetS();
while ( vstartdate @=> vMonthDim);
cellputN(STRINGTONUMBER('1'), .. , vstartdate, ..);
End;
In terms of the cube and dimension designs, there are 6 common dimensions, the 1 dimension that is different, for cube 1, its a month dim, with Jan-15 etc periods as 0 level nodes and in cube 2, the startdate and finishdate is string values within the nodes intersections.
I will continue to work on it.
Thank you once again sir.
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
If you compare strings these should be in the same format.
For example: yyyymmdd on both sides of the inequality.
Can you provide more information ?
What business problem do you want to tackle ?
Which cubes do you have ? Which dimensions ?
Besides the looping, do you use a view for the data source of the process ? Or a different data source ?
Provide relevant screenshots of dimension elements.
Etc.
For example: yyyymmdd on both sides of the inequality.
Can you provide more information ?
What business problem do you want to tackle ?
Which cubes do you have ? Which dimensions ?
Besides the looping, do you use a view for the data source of the process ? Or a different data source ?
Provide relevant screenshots of dimension elements.
Etc.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 58
- Joined: Thu Jul 03, 2014 1:14 pm
- OLAP Product: cognos
- Version: 10.2.2
- Excel Version: 2010
Re: CELLPUTN
Hello Wim
The format is the same - both strings and Month Period nodes.
Cube 1 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Details ( 2 elements, startdate (Intersection: Jan-15), finishdate (intersection: Dec-34))
Cube 2 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Months (Jan-15, ..,, ... , Dec -34)
Just using data views from cubes.
I am attaching a similar format to what I am currently looking at with all referred dimensions.
Thank you for the help
The format is the same - both strings and Month Period nodes.
Cube 1 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Details ( 2 elements, startdate (Intersection: Jan-15), finishdate (intersection: Dec-34))
Cube 2 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Months (Jan-15, ..,, ... , Dec -34)
Just using data views from cubes.
I am attaching a similar format to what I am currently looking at with all referred dimensions.
Thank you for the help
- Attachments
-
- Practise.jpg (97.87 KiB) Viewed 11495 times
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
It complicates matters if StartDate and/or FinishDate is empty, but if they were both non-empty or both empty, then this will work:
Create a view on Cube1 in the Prolog section of the Advanced tab. Select in the dimension Details on element StartDate.
That way, you can loop over all non-zero StartDate cells
Also, do a zero out on Cube2 for the relevant cells
In the Data tab of the Advanced tab, write:
This further assumes:
- input in Cube2 has the same month names as in dimension Months can be found
- the months in dimension Months are sorted, index-wise, from the oldest (index 1) to the newest month (highest index). If this is the case, and you can live with the fact that indexes in that dimension should ALWAYS be correct, this will work without further changes.
Honestly, I do not know why you would write:
Instead of
Why is that ?
Create a view on Cube1 in the Prolog section of the Advanced tab. Select in the dimension Details on element StartDate.
That way, you can loop over all non-zero StartDate cells
Also, do a zero out on Cube2 for the relevant cells
In the Data tab of the Advanced tab, write:
Code: Select all
i = Dimix( 'Months', SValue);
While( i <= Dimix( 'Months', CellGetS( 'Cube1', UniqueID, Place, Type, unit, model, measure, 'FinishDate') ));
CellPutN( 1, 'Cube2', UniqueID, Place, Type, unit, model, measure, Dimnm( 'Months', i ));
i = i + 1;
End;
This further assumes:
- input in Cube2 has the same month names as in dimension Months can be found
- the months in dimension Months are sorted, index-wise, from the oldest (index 1) to the newest month (highest index). If this is the case, and you can live with the fact that indexes in that dimension should ALWAYS be correct, this will work without further changes.
Honestly, I do not know why you would write:
Code: Select all
cellputN(STRINGTONUMBER('1'),
Code: Select all
cellputN(1,
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Hi Mithun, Im guessing you want to turn this
Into this?
Have you considered doing it in TM1 rules?
I think the main thing you need is give your time dimension a number attribute that it can use for period checking rather strings see below for an example
Into this?
Have you considered doing it in TM1 rules?
I think the main thing you need is give your time dimension a number attribute that it can use for period checking rather strings see below for an example
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: CELLPUTN
Rules are a possible option but if it needs to be fed then doing so will at best be annoying and at worse a huge memory drain (due to likely over feeding.)
On the TI approach I would avoid using the dimension index due to the issues mentioned above, even if the order is correct now it is easy for someone to mess it up.
As long as you have a "NextPeriod" attribute populated then it's a simple loop:
Also your post suggests that you don't have a measures dimenion in cube 2, I won't go in to it too much here as it's done to death in this forum but I would certainly reconsider it if I were in your position.
On the TI approach I would avoid using the dimension index due to the issues mentioned above, even if the order is correct now it is easy for someone to mess it up.
As long as you have a "NextPeriod" attribute populated then it's a simple loop:
Code: Select all
sTargetPeriod=CellGetS('Cube1',vDim1...,'StartDate');
sEndPeriod=CellGetS('Cube1',vDim1,...,'EndDate');
While ( sTargetPeriod @<>'');
CellPutN(Value,'Cube2',vDim1,...,sTargetPeriod);
If ( sTargetPeriod @= sEndPeriod );
sTargetPeriod = '';
Else;
sTargetPeriod = Attrs ('PeriodDim', sTargetPeriod, 'NextPeriod');
EndIf;
End;
Declan Rodger
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Have you considered doing it in rules, that way you dont have to run a TI after every change.
Here are the cubes i've created based upon yours.
And here are the rules for each of the cubes.
It may look a bit complicated, but its really good and makes TM1 faster as it doesn't feed cells it doesn't need to and as it looks like your using a lot of from and too dates that may be a good thing. it depends on how you feel about conditional feeding.
What happens is that when you change one of the dates in your Start End cube the string change always fires the feeders (unlike numeric cells).
The feeder section on a change of start date or end date fires forward from the start date and backwards from the end date but only where it falls in the range, so its all automatically fed.
What complicates it more is that you are using blank, so i've used as a default
start date blank = from the first period,
end date blank = to the last period.
Here are the cubes i've created based upon yours.
And here are the rules for each of the cubes.
It may look a bit complicated, but its really good and makes TM1 faster as it doesn't feed cells it doesn't need to and as it looks like your using a lot of from and too dates that may be a good thing. it depends on how you feel about conditional feeding.
What happens is that when you change one of the dates in your Start End cube the string change always fires the feeders (unlike numeric cells).
The feeder section on a change of start date or end date fires forward from the start date and backwards from the end date but only where it falls in the range, so its all automatically fed.
What complicates it more is that you are using blank, so i've used as a default
start date blank = from the first period,
end date blank = to the last period.
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
So the proof of the pudding is
Where you can see the zero is unfed, which means that its not going to feed any other cubes and waste CPU.
When we suppress zeros we get.
Which is still correct.
If you still want to do it in TI give me a shout, it will be quite easy to go through it in TI.
Where you can see the zero is unfed, which means that its not going to feed any other cubes and waste CPU.
When we suppress zeros we get.
Which is still correct.
If you still want to do it in TI give me a shout, it will be quite easy to go through it in TI.
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
In TI it's easier as Declain says, here is a starter for 10 that should put you on the right track, i've used bedrock to create the view that its going to use,
Based upon the dimension we discussed earlier.
Can be achieved quite simply by using the following TI process
And the following code
Its just a simple example and you will need to add all your other dimensions in, but hope it will be a starter for 10.
Regards Jules
Based upon the dimension we discussed earlier.
Can be achieved quite simply by using the following TI process
And the following code
Its just a simple example and you will need to add all your other dimensions in, but hope it will be a starter for 10.
Regards Jules
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
Jules,
Thank you for your contribution.
Why do you use the destination cube as the data source ?
To me it makes more sense to use the input cube as the data source. With an extra measure (Treated, 0 or 1) and a simple loop as explained above, this is not difficult to do.
Thank you for your contribution.
Why do you use the destination cube as the data source ?
To me it makes more sense to use the input cube as the data source. With an extra measure (Treated, 0 or 1) and a simple loop as explained above, this is not difficult to do.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Hi Wim,
Thats quite easy to explain, its for simplicity for mithun to follow.
We are using a TI view instead of loops, whilst also dealing with :
Mithuns blank in 'Start Date' meaning from the first period of the dimension and
Mithuns blank in 'End date' being the end of the dimension
This can also be completed in a number of ways and as the old saying goes, there are many ways to skin a cat.
You have insisted on another measure element or dimension having a zero or a one?
Why is that?
I have also noticed your negativity towards users
Why is that?
Could it possibly because its easier to be be a critic than a creator?
Thats quite easy to explain, its for simplicity for mithun to follow.
We are using a TI view instead of loops, whilst also dealing with :
Mithuns blank in 'Start Date' meaning from the first period of the dimension and
Mithuns blank in 'End date' being the end of the dimension
This can also be completed in a number of ways and as the old saying goes, there are many ways to skin a cat.
You have insisted on another measure element or dimension having a zero or a one?
Why is that?
I have also noticed your negativity towards users
Why is that?
Could it possibly because its easier to be be a critic than a creator?
Last edited by JulianS on Sun Jul 09, 2017 10:25 am, edited 2 times in total.
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Hi Mithun,
Here is a example that doesn't need any extra measures as its all calculated in the TI.
Its a bit more complicated than the previous one, but Wim was having issues with understanding the logic of using a view of the target cube to iterate through all the cells
and process them in turn.
So here is a quick TI I knocked together to help you doing it from the Wims suggestion (but hopefully a bit more constructively for you).
Cube Mithun_Start_End -> Mithun Periods
No new measures are required.
And to quote our friend ' Its not difficult to do'
Good luck my friend, and happy TM1ing in the future!
Here is a example that doesn't need any extra measures as its all calculated in the TI.
Its a bit more complicated than the previous one, but Wim was having issues with understanding the logic of using a view of the target cube to iterate through all the cells
and process them in turn.
So here is a quick TI I knocked together to help you doing it from the Wims suggestion (but hopefully a bit more constructively for you).
Cube Mithun_Start_End -> Mithun Periods
No new measures are required.
And to quote our friend ' Its not difficult to do'
Good luck my friend, and happy TM1ing in the future!
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
JulianS,JulianS wrote:I have also noticed your negativity towards users
Could it possibly because its easier to be be a critic than a creator?
Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.
I gathered more than 1,400 posts here. I gathered another 40,000 posts on public Excel and VBA forums. All for free.
I have been a Microsoft Most Valuable Professional, 4 times consecutively, for apparently not creating anything and for not boosting the communities I got the awards for being the best-in-class giving critique
There is also my public website with multiple hundreds of (lengthy) Excel and TM1 articles, both English and Dutch, and code.
All for free and I guess - please do speak up if it's not correct - that I created something.
Check out the "Useful code, tips and tricks" subforum (http://www.tm1forum.com/viewforum.php?f=21): I invite you to create as much as I did and still do. The last 3 topics are mine with 2,600 lines of TI coding, excluding whitespace. But I don't create
The bottom-line goes like this: please behave yourself or you will be kicked out soon.
On-topic:
even with no single input in your input cube, you will loop over ALL lowest-level cells in the target cube. You do understand the function "ViewExtractSkipZeroesSet" ?
Even though it's not as simple as your approach, I agree, you should know that simplicity comes with a cost.
If TI is used in this case, it has to be quick since users will execute the process quite often.
If you want to loop over all those potentially hundreds of thousands of cells, which is even not necessary: good luck.
Absolutely, my TM1 knowledge is way below average, I do apologize for that. I am taking courses to get up to speed with the softwareJulianS wrote:Its a bit more complicated than the previous one, but Wim was having issues with understanding the logic of using a view of the target cube to iterate through all the cells and process them in turn.
Apparently it was too difficult for you because your ElseIf in the Data tab can be flat out wrongJulianS wrote:And to quote our friend ' Its not difficult to do'
For your information. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon. But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed. Do you understand the mechanics of cubes and that the number of combinations / cells runs into millions quickly ? You will soon change your mind once you set up allocations in a TM1 model for real and you start from the target cube Happy looping! Above you wrote about not wanting to waste CPU
Finally, if you want to be constructive, post code and not only screenshots. Do users need to type code again ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Dear Wim,
Many thanks to your insight and your knowledge and your off topic points, all duly noted.
I would query a few points you raised though.
You Quote :
In my career I have worked very closely with Steve Vincent i'm sure you know him as you congratulated him on his postings.
I however have always avoided this forum as i think its confrontational.
For example just look at this thread to see the negativity. Some of us want to help people, not slap them down and made to feel foolish for asking for help.
You Quote:
I only signed up three days ago as I was contacted directly by Mithun an old colleague of mine from years ago regarding the issue as he wasn't getting anywhere on here and was not following the logic, otherwise I wouldn't have joined.
You Quote
If helping somebody is grounds for kicking somebody out then so be it.
On Topic
You Quote
This is how it it has been written to work.
I'm sorry if you think that my helping another person is wrong, but that's the way it is.
I'm sorry that you feel so upset but I do not know why.
Kind Regards
Julian
Many thanks to your insight and your knowledge and your off topic points, all duly noted.
I would query a few points you raised though.
You Quote :
Some people who work as developers do not post on this forum but would like to help people and not just be mean.Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.
In my career I have worked very closely with Steve Vincent i'm sure you know him as you congratulated him on his postings.
I however have always avoided this forum as i think its confrontational.
For example just look at this thread to see the negativity. Some of us want to help people, not slap them down and made to feel foolish for asking for help.
You Quote:
That's very noble and commendable, and is an asset to us all.Check out the "Useful code" subforum here: I invite you to create as much as I did and still do.
Please behave yourself or you will be kicked out soon.
I only signed up three days ago as I was contacted directly by Mithun an old colleague of mine from years ago regarding the issue as he wasn't getting anywhere on here and was not following the logic, otherwise I wouldn't have joined.
You Quote
Ok, what am I supposed to have done now? Help somebody? Help solve somebodys issue? Is that grounds for being kicked out.Please behave yourself or you will be kicked out soon.
If helping somebody is grounds for kicking somebody out then so be it.
On Topic
You Quote
Let me take this point by pointFor your information. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon. But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed. Do you understand the mechanics of cubes and that the number of comnbations / cells runs into millions quickly ? You will soon change your mind you set up allocations and start from the target cube
A: I think your assumptions are wrong if you look at the data, and the TI, suppress zeros are deliberately turned off therefore if no start date and no end date are in place, all rows would be 1, therefore we would need to traverse the entire dimension setting them to 1.1. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon.
This is how it it has been written to work.
Thank you for your complement, but please see above answer. if your assertion is true you would only be able to process a record where either a start date or end date is present. In this case, I think you'll find this is an employee, where if no start date or end date is supplied they they will be working for all periods until TM1 is instructed otherwise. This is taken care of in both the TI processes. If however the data always had a start or end date the Ti would be as you say.But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed.
A : I surely do. Its a good job TM1 only holds non zeros in memory huh? Otherwise we'd all be in trouble.Do you understand the mechanics of cubes and that the number of comnbations (sic) / cells runs into millions quickly ?
A: Which TI are you talking about the one I generated as a simple example? That was just a demonstration to show the logic as explained above.You will soon change your mind you set up allocations and start from the target cube
I'm sorry if you think that my helping another person is wrong, but that's the way it is.
I'm sorry that you feel so upset but I do not know why.
Kind Regards
Julian
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: CELLPUTN
Dear Julian,
It's humble that you help others, we all do. Not only during the day, also in the evening and during weekends. However, your negative comments addressing me personally in this topic are very unprofessional and even low-level. I leave it to the moderators to decide how to rate these picky comments.
What's your point about having worked with Steve Vincent ? That you don't make picky comments ?
On topic: you deliberately turn off skipping empty cells. You assume that empty starting date and empty ending date means: all time periods should be 1. I would assume that all periods should be 0 in that case. A cube with 7 dimensions can have a lot of empty cells and combinations that are not "occupied" business-wise - these are all employees that work all periods in your understanding ? In your simple 2D cube you won't see the effect of not skipping empty cells. The real cubes are not 2D however.
Wim
It's humble that you help others, we all do. Not only during the day, also in the evening and during weekends. However, your negative comments addressing me personally in this topic are very unprofessional and even low-level. I leave it to the moderators to decide how to rate these picky comments.
What's your point about having worked with Steve Vincent ? That you don't make picky comments ?
On topic: you deliberately turn off skipping empty cells. You assume that empty starting date and empty ending date means: all time periods should be 1. I would assume that all periods should be 0 in that case. A cube with 7 dimensions can have a lot of empty cells and combinations that are not "occupied" business-wise - these are all employees that work all periods in your understanding ? In your simple 2D cube you won't see the effect of not skipping empty cells. The real cubes are not 2D however.
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- JulianS
- Posts: 20
- Joined: Fri Jul 07, 2017 1:34 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2013
Re: CELLPUTN
Dear Wim,
Agreed. But we should also encourage people not feel worried to post their issues.
Well thats a two way door to get respect you have to show respect.
Please explain how I've personally hurt your feelings.
Please explain why my comments are low level compared with say.
Thread Planning Analytics Local 2.0: Rules Calling Multiple Hierarchies
in which you say
I've worked with Steve on and off for 10 years in TM1 on lots of projects.
The three days you quote are not really relevant.
Your above quote
On topic:
The TI above is an 'example' and is using two dimensions because its using a demo cube with two dimensions.
When you change it for an cube with more dimensions, you would adjust your view accordingly, say for example a scenario dimension were to be added, we would use a single 'N' level in the cube view, very rarely would you run an update TI against an entire seven dimensional cube as you could inadvertently effect other versions. I would also not be using CubeClearData, but ViewZeroOut with a bespoke view zero out view all of which would have parameters.
I think the issue here is that these are examples to show functionality, not finished products, as I said in the post 'Starter for 10'.
view.
Look i'm not going to post any more on this thread as I feel its now as i't become something it shouldn't have.
It's humble that you help others, we all do. Not only during the day, also in the evening and during weekends.
Agreed. But we should also encourage people not feel worried to post their issues.
However, your negative comments addressing me personally in this topic are very unprofessional and even low-level.
Well thats a two way door to get respect you have to show respect.
Please explain how I've personally hurt your feelings.
Please explain why my comments are low level compared with say.
or :Honestly, I do not know why you would write:
CODE: SELECT ALL
cellputN(STRINGTONUMBER('1'),
Instead of
CODE: SELECT ALL
cellputN(1,
Why is that ?
Thread Planning Analytics Local 2.0: Rules Calling Multiple Hierarchies
in which you say
RTFM
Thats fine with me. If they kick people out for trying to help then thats ok.I leave it to the moderators to decide how to rate these picky comments.
Some of us have worked with TM1 for a long time and have not posted.What's your point about having worked with Steve Vincent ?
I've worked with Steve on and off for 10 years in TM1 on lots of projects.
The three days you quote are not really relevant.
Your above quote
Some of us have kept an eye on this forum even though we don't post.Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.
I've already told you in the previous note that i'm sorry that i've hurt your feelings.That you don't make picky comments ?
On topic:
No that the crux of the issue, having actually spoken to Mithun, this is the behaviour he wants.you deliberately turn off empty cells. You assume that empty starting date and empty ending date means: all time periods should be 1. I would assume that all periods should be 0 in that case.
The TI above is an 'example' and is using two dimensions because its using a demo cube with two dimensions.
When you change it for an cube with more dimensions, you would adjust your view accordingly, say for example a scenario dimension were to be added, we would use a single 'N' level in the cube view, very rarely would you run an update TI against an entire seven dimensional cube as you could inadvertently effect other versions. I would also not be using CubeClearData, but ViewZeroOut with a bespoke view zero out view all of which would have parameters.
I think the issue here is that these are examples to show functionality, not finished products, as I said in the post 'Starter for 10'.
In the two dimensional cube yes, but as discussed above on a cube with more dimensions you would tailor you view accordingly and only run the TI against a 'slice' of the cube and you would be using a ViewZeroOut and a bespokeA cube with 7 dimensions can have a lot of empty cells and combinations that are not "occupied" business-wise - these are all employees that work all periods in your understanding ?
view.
Look i'm not going to post any more on this thread as I feel its now as i't become something it shouldn't have.
-
- Posts: 58
- Joined: Thu Jul 03, 2014 1:14 pm
- OLAP Product: cognos
- Version: 10.2.2
- Excel Version: 2010
Re: CELLPUTN
Hello
Thank you so much for all your help guys. I have learnt alot technically and I have received so much support from you all.
Jules, thank you so much for coming onto the forum on my request, I appeciate all the help you've given me. I have done both solutions and they both work. Again, thank you so much.
Mithun
Thank you so much for all your help guys. I have learnt alot technically and I have received so much support from you all.
Jules, thank you so much for coming onto the forum on my request, I appeciate all the help you've given me. I have done both solutions and they both work. Again, thank you so much.
Mithun
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: CELLPUTN
Happy to hear that your problem has been solved.Mithun.Mistry1103 wrote:Jules, thank you so much for coming onto the forum on my request, I appeciate all the help you've given me. I have done both solutions and they both work. Again, thank you so much.
Just to offer an alternative to the rule approach to any future users that stumble across this thread which may hopefully be easier to follow as it just focuses on populating the first period and then rolling on until the end period is found.
I caveat this by saying you should probably throw a rule at the start to force all cells to zero if the End Date is not populated.
Also with rules like this you need to be careful that you don't go past the stack overflow point; it is unlikely you will when working with a month based dimension (assuming that all actuals are done by STET'd TI values and it only calculates forecast) but if it went down to week or day level, or if you are dealing with showing the "active life" of long-term construction projects etc (we had some that exceeded 20 years at a previous company I worked with) then you could blow it.
Rule in Cube 2
Code: Select all
[]=N:
# First populate a 1 if the Month is equal to that held in "Start Date" in Cube 1
If ( DB('mithun_1',!mithun_id,'Start Date') @= !mithun_time,
1,
# If the previous month from this one is equal to that held in "End Date" in Cube 1 then set to zero
If ( AttrS ( 'mithun_time', !mithun_time, 'Prior_Period' ) @= DB('mithun_1',!mithun_id,'End Date'),
0,
# Otherwise pull value from the previous month (1 or 0)
DB('mithun_2',!mithun_id, AttrS ( 'mithun_time', !mithun_time, 'Prior_Period' ) )
)
);
Code: Select all
[{'Start Date','End Date'}] => DB('mithun_2',!mithun_id, DB('mithun_1',!mithun_id,'Start Date'));
Code: Select all
[{'Start Date','End Date'}] => DB('mithun_2',!mithun_id, DB('mithun_1',!mithun_id,'Start Date'));
Declan Rodger