Trim Data

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Solved3.42K viewsFormulas and Functions

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.

Thank you

Changed status to publish

Hi Vishnu,
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.
Edited answer
You are viewing 1 out of 7 answers, click here to view all answers.