I have a matrix Revenue that has foretasted values for 34 Years. The start date is unique for every Account ID. I need foretasted values for only 30 years(i.e 360 Values) and as every account has unique start date the end date will also unique. Account 1 starting on Nov 2013 should end on Nov 2043. Any cells after that should be null or zero
I tried finding the first value and moving to 360 cells and making all other cells as "0" but first and last function returns values for whole range not each row.
Can some one please suggest a solution for this.
Of course, this formula can be simplified a little bit. For this purpose it is necessary to use MY Range Defs::From..To values. So, the formula now will be:
- : = ClearError( ValueAt( SubList( Datas::Year:Month, MY Range Defs::From, MY Range Defs::To – 1 ), #Year:Month – UI Calcs Parameters::Start Dates Shifting – MY Range Defs::FnzVP + 1 ) )
By the way, the same result will be yielded by the following formula:
- : = ClearError( ValueAt( SelectBetween( Datas::Year:Month, #Year:Month, MY Range Defs::From, MY Range Defs::To – 1, 1 ), #Year:Month – UI Calcs Parameters::Start Dates Shifting – MY Range Defs::FnzVP + 1 ) )
Now let’s talk a little bit about ValueAt(List, Offset). SubList() (or SelectBetween()) forms a List. I think there is no difficulty in understanding here. But the Offset secret is that the List values are displayed in those TimeLine positions in which the Offset >= 1. Knowing about this secret, you will understand easily Offset expression in my model. Thus, in my model the data are shifted in the TimeLine positions where :
- #Year: Month >= UI Calcs Parameters :: Start Dates Shifting + MY Range Defs :: FnzVP – 1,
- #Year:Month – pointer position of TimeLine component;
- UI Calcs Parameters::Start Dates Shifting – shifting of data in months;
- MY Range Defs::FnzVP – position of the first non-zero value in the source data.
Thanks for posting the solution. The solution works perfectly but it was little confusing in understanding this formula.
: = ClearError( ValueAt( [color=#80BF40:33gryraz]SubList( Datas::Year:Month,
MY Range Defs::FnzVP + UI Calcs Parameters::Start Dates Shifting,
MY Range Defs::To – 1 )[/color:33gryraz],
#Year:Month – UI Calcs Parameters::Start Dates Shifting – MY Range Defs::FnzVP + 1 ) )
In this formula I understand that Sublist has an input of Range with first and last value. In the previous model you have posted using select between, you have calculated the first and last values in First_Last matrix. Why cant we use those values in this sublist function. We know the first value and we are looking for a range that is 10 years from start date till end date(i.e first value+120, last value). I have tried this but it is not working. ValueAt function used here is also not clear. You have been very helpful and i appreciate if you could make it easier to understand.
1. I think this problem is not solved by means of SelectBetween() function, so I used the SubList() and ValueAt() functions.
2. For convenience of perception, the beginning of subrange QM marks in the black color, and the end – in the red color.
3. Change two parameters in the UI Calcs Parameters matrix. Start with small quantities, gradually increasing them to values you need.
In the same example what would be the selectbetween function to consider a range of (120-340), i.e for account id 1: I would like to consider values between 2024 Jan and 2043 Dec similarly for other accounts values between 10years from start date and end date are considered.
Thank you for your help.