Do not overwrite Values for Dates older yesterday - TM1 TI

Post Reply
CTM1K
Posts: 6
Joined: Fri Apr 05, 2019 11:49 am
OLAP Product: PAx, PAw, TM1
Version: 4.0.x
Excel Version: 2016

Do not overwrite Values for Dates older yesterday - TM1 TI

Post by CTM1K »

Hi all,

I've used this forum to solve my issues or questions I had, successfully, until now.

What I want to achieve:

I do not want to touch or sum up values in a Cube for a Date that is older than the day before yesterday (previous period). These values have to stay untouched and unchanged.
But this does not apply for all the data stored. I also have variable Elements, which need to be updated even for older Dates (periods) daily.

Right now, I'm running a ZeroOut process that clears all these variable Elements, which allow me to have updated values daily, which is not a sum of all periods the cube has been updated(this one does work without issues).

For my other criteria, I'm using a CellIncrementN (see code below), which unfortunately is adding values to the day before yesterday. I hope you guys can help me out with this one.

Code: Select all

CellincrementN( (1), csCubeTarget, csDateAP, vCountryName, vPatientNum, vPatStatusDescr, psInput, psValue);
csDateAP is the date of the day before yesterday. I'm grabbing this one from a sys.config Cube, which is populating the day before yesterday via a rule to a string element within the sys.config Cube. DD.MM.YYYY

vPatientNum is a SerialID, vPatStatusDescr is a Description of the Product, psInput is either manual or automatic and psValue is either String or Numeric.

I guess this one line of code is not sufficient to fulfill the requirements of my needs. But my thought process behind that was, if I'm able to grab the date of yesterday (i.e. Aug 24th), usually the CellincrementN should only write values to the specific date Element, right? Instead it is adding values to days before yesterday, instead of "skipping" them.
Emixam
Posts: 139
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by Emixam »

Hello,

What is the source of your process ?

CTM1K wrote: Mon Aug 24, 2020 3:17 pm

Code: Select all

CellincrementN( (1), csCubeTarget, csDateAP, vCountryName, vPatientNum, vPatStatusDescr, psInput, psValue);
csDateAP is the date of the day before yesterday.

usually the CellincrementN should only write values to the specific date Element, right? Instead it is adding values to days before yesterday, instead of "skipping" them.
If csDateAP is the date of the day before yesterday defined in the prolog and you are using that element/variable in your CellIncrementN, it means it will increments an existing numeric cell value by (1).

There is a difference between CellIncrementN and CellPutN

I'm not sure about your requirement but if you want to send value from csDateAP to another date element, you should do something like this:
1. Generate a view with csDateAP and use it as data source
2. In the prolog, define the date element where you want to input your data ( ex: sToday = ATTRS( 'Date Dimension', csDateAP, 'Next Day'); )
3. In the data tab do something like this:

Code: Select all

CellIncrementN( Value, csCubeTarget, sToday, vCountryName, vPatientNum, vPatStatusDescr, psInput, psValue
CTM1K
Posts: 6
Joined: Fri Apr 05, 2019 11:49 am
OLAP Product: PAx, PAw, TM1
Version: 4.0.x
Excel Version: 2016

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by CTM1K »

Hi Emixam, thanks for the quick reply!

I'm not sure what source you are referring to, the database? Its a SQL database that is updated at 3 a.m. EST, 9 am CET. Thats why I need to refer to yesterday.

Basically it is only that one line of code.

csDateAP is a numeric value, translated into a string using a rule in a config cube:

Code: Select all

# Calculating yesterday:
[ 'Yesterday' , 'value_n' ] = N:DAYNO(Today(1)) - 1;

#Translation of a numeric date value into a string
[{'Yesterday'} , 'value_s'] = S: SUBST( DATE( ['value'], 1), 9, 2) | '.' | SUBST( DATE( ['value'], 1), 6, 2)| '.' | SUBST( DATE( ['value'], 1), 1, 4);

csDateAP Code:

Code: Select all

csDateAP = CellGetS( 'Config', '#, 'Yesterday', 'value_s'); 
CellIncrement:
I'm using a (1) as the database I'm referring to has no value I can use to create a sum. I am counting the amount of two products, the one product has to be counted flexible. For the other product, once its sold, the number should not change. Unfortunately my line of code is adding always adding older dates..
Emixam
Posts: 139
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by Emixam »

Hello CTM1K,

I read your post 2-3 times and I still don't understand what you are trying to achieve. I'm sorry but English is not my first language (not even my second!) so maybe someone else could jump in and help you.

Have a good day !
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by scrumthing »

I read it a couple of times as well and still don't get it.
If you want to avoid a cellincrement for a specific date you can simply use an if-clause for that. But I am not sure if that in any way helps you because i am really not sure what you want do achieve.
There is no OLAP database besides TM1!
CTM1K
Posts: 6
Joined: Fri Apr 05, 2019 11:49 am
OLAP Product: PAx, PAw, TM1
Version: 4.0.x
Excel Version: 2016

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by CTM1K »

English is obviously not my mother language, but I try to reprhase my issue.

With a TI Process, I am writing two different Values to a Cube.

One of these Values have to be updated with the new Value each day. The process is executed daily and Monday numbers for Value "A" might change throughout the week, there might be a delay in updating the data, as its updated manually within the system.

For the second value "B" I want to achive the opposite. Once written to the Cube, value "B" should never change, as it is Stock related, and I want to know how does the Stock changes throughout the cause of a week, month or year. The datasource is a SQL Query. The query is unfortunately updating the Stock for previous periods daily. So I need a process, to overcome a daily update and to have "fixed" Values for previous periods...

Its kinda hard to explain and I hope, this is sufficient..
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Do not overwrite Values for Dates older yesterday - TM1 TI

Post by scrumthing »

The easiest solution might be if I understood you correctly to make it two processes. One for each value and one is run weekly and the other on a daily basis. The SQL query for the weekly one could be changed by using the sql server datetime functions in a WHERE clause.
There is no OLAP database besides TM1!
Post Reply