Extract Year from Date Column in the layout using algorithm.

Hello all,

Is it possible to extract year from Date cube (the First Column in the Image) by using trim, Right/Left, Year function ?!

by creating a Column algorithm in the layout itself ?

I am trying to get that and used various combinations of the same but no luck.

can any one please guide ?!

for example:

In the image below there is a first Column with Date, i just need to extract Year from it and display in last column.

Looking for help on this one.

Warm Regards,

Malav

Find more posts tagged with

Accepted answers

All comments

Mateo DE LOS RIOS

Hi Malav,

From what i see the "Year" function works for the ETL Datareader and Nexel.

You can try to test the "Nexel" functionalitie.

To use it, set your column algorithm formulas to "Single rule" :

Then, click on "Edit formulas" in the layout sliding toolbar :

Then double click on the desired cell, edit the formula and save :

Close the pop-up and your cell should show you the year.

Let me know if it's helping.

Best regards,

Mateo DE LOS RIOS

Dietmar Jeschkeit

Hi Malav Shelat,

why don´t you use an algorithm with right(a,4) - a is the block with the date - and set it to text to avoid the "." for thousend ?

You can also use the Excel mid function if you have a datetime in block a.

Dietmar

Previous Member

Hi Dietmar Jeschkeit,

right(a,4) wont work, already tried along with formatting as well. No Luck.

thanks for the reply

regards,
Malav

Previous Member

Hi Mateo DE LOS RIOS,

Thank you for your reply, actually i was aware about this Nexel function but i was just hoping if we could do this without nexel as i want to use that in procedure Dataflows. Is that possible in our Layout itself with Column Algorithm and not using nexel.

do you think that's possible ?

Regards,

Malav

Paul Wyatt

Hi Malav,

I have not read the complete thread so excuse me if I missed the point but It looks like you're treating a figure as a text value. If so, you must make the destination datatype 'text' also. Image 1 shows the NEXEL formula =year([@a;*;*]) in columns b and c. Column b is a text cube but could easily be a block created within NEXEL and then changed to datatype: TEXT.

The reason I illustrate a cube is because I also wanted to say that NEXEL can be used server-side as an alternative to dataflows. Used this way, NEXEL becomes a very powerful ETL tool.

Image 1 the NEXEL formula =year([@a;*;*]) has been placed in columns b and c. Column b is a TEXT cube whilst Column c is a numeric column.

Hope this helps

Dietmar Jeschkeit

Sorry,

I forgot it´s a date cube. The date internal in BOARD is like in Excel 1 for 1.1.1900 and then +1 for each day. That's why, of course, the year doesn't come with right(a,4).

Previous Member

Exactly ...

Previous Member

Hi Paul Wyatt - Avison Young UK,

Thank you for your reply, actually i am aware about this Nexel function but i was just hoping if we could do this without nexel as i want to use that in procedure Dataflows. Is that possible in Layout itself with Column Algorithm and not using nexel.

do you think that's possible ?

Regards,

Malav

Previous Member

Hi Malav,

Looks like it's possible in a column algorithm by reproducing Excel number format logic. That is to say divide the date value by 365.25 and adding 1900 :

trunc((a-1)/365.25+1900)

The -1 is for 31st december that would give next year without it. Tried it with several dates and seem to be always working.

Pietro Di Cino

HI,

A simple way is to set a block into the dataview with the entity year. Simple and smart ;-)

Dietmar Jeschkeit

Hi Pietro,

but the Question was extracting the year from a date cube. And the block with Entity year will only work if you have the time by row.

Previous Member

Hi Basil Henrion,

Thank you very much for helping out, this is exactly the solution i was looking for.

Warm regards,

Malav