Expression Editor (Date Calculations) in Calculation View Question

[Former Member]

Hey guys, I have 2 simple questions. I'm using Expression Editor in Calculation View to do simple calculations:

1. How can I subtract between 2 dates to get the number of days difference? For example:

"SSEDD" - "FSAVD"

(Both are DAT fields in Table AFVV). I get an error for this simple equation. Also, I tried daysbetween() function to no avail: daysbetween ("SSEDD" - "FSAVD")

How can I subtract between dates to get number of days in between?

2. How can I get current date in Expression Editor, what's the syntax? I tried now() functionality, but using it in formula gives me error.

My formula is (including table name, but not in editor):

((AFKO-GFTRP - now()) / (AFVV-SSEDD - AFVV-FSAVD)

How should I write the above formula in editor?

[Former Member · Accepted solution]

Hi Mic,

1. The correct syntax is daysbetween("FSAVD","SSEDD")

2. now() should give you the current date.Simply create a calculated attribute with type as DATE and just enter now() and test it. You will be able to see the current date as output for that column.

In the expression you have entered, I assume all fields are of DATE type. In that case, please use daysbetween function within the formula to achieve this.

Thanks,

Anooj

[Former Member · Accepted solution]

Hey Anooj.

You're syntax is correct, it seems there is a simple issue I think I figured out.

I'm dividing in my formula. If the second part of my formula is a 0, that will cause an error cause we can't divine by 0 in math.

So I believe I need an if().

So I have to parts to my formula daysbetween(AFKO-GFTRP - now()) / daysbetween(AFVV-SSEDD - AFVV-FSAVD))

Part 1 divided Part 2, I have to make sure part 2 is not 0.

How can I add an if() function to the formula?

So the logic should be:

If Part 2 > 0................then the formula should be performed.

How does if() syntax work? Or do I need another function to do this?

[Former Member · Accepted solution]

Hi Mic,

Yes you can use if() in this case. The syntax of if is:

if(a=b,'True','False)

In your case, the formula could be:

if(daysbetween(AFVV-SSEDD - AFVV-FSAVD))>0,

daysbetween(AFKO-GFTRP - now()) / daysbetween(AFVV-SSEDD - AFVV-FSAVD)),0)

similar to saying if X > 0, then Y/X else 0

You can find the syntax of these functions if you refer to the section CE_CALC in this guide:

http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Thanks,

Anooj

[Former Member]

Guys I'm having a simple problem that's frustrating me to death.

I have a date field ERDAT in table PRPS.

All I need to do is:

ERDAT > (now() - 100)

But it's not working.

ERDAT is defined as NVARCHAR with length 8 in HANA. So I created a calculated column in an attribute view that has the formula "now() - 100". I used practically all data types to define it (NVARCHAR, DATE, int, etc.) and even tried to conversion functions in expression editor (ex. date(), string(), int().

When I do """ERDAT > "my calculated column" (now() - 100)""" at the filter in the calculation level its not recognizing the date.

I just need to filter data where it only brings data that's only 100 days old.

Any tips? I'm sure there's something I'm missing. Something to do with data types. =(

[former_member212706]

Hello Mic,

     I would suggest you to craeate a calculated attribute and a filter to achieve this.

use the following :

if(daysbetween(now(),date("ERDAT")) < 100, 1 ,0). Now we can filter the rows with this attribute.

Hope it helps.

Thanks,

Rajesh.

[Former Member]

Anooj, at this point I'm doing SQL.

It seems I have to use max in my formula. Every Order has many dates and I have to get max date (most current).

Is there anyway to do max without sql? Expression Editor and any other way graphically?

[Former Member]

Yes you can do it within the analytic view. Add your date as a private attribute but make it hidden. Then create a calculated measure of type date, add the hidden private attribute date into the expression editor, change the aggregation type to 'max' and set 'calculate before aggregation'.

Thanks,

Anooj

[Former Member]

Hey Anooj,

So I'm trying the above.

I'll show my scenario specifically below.

Order              Date

10                  10132012

10                  10242012

10                  10132012

10                  10192012

20                  10132012

20                  10182012

I have to get MAX date for order.

So my ideal situation should look like this where I have another field.

Order              Date                   Max Date

10                  10132012              10242012

10                  10242012              10242012

10                  10132012              10242012

10                  10192012              10242012                

20                  10132012              10182012

30                  10182012              10182012

I would need max date field to do calculations in calculation View.

So this is what I did so far according to your directions but don't see the Max_date on the output when I do data preview. I need that column in calculation view.

[Former Member]

Hi Mic,

I thought you only wanted to see the orderno and maxdate columns like

orderno      maxdate

10             10242012

20             10182012

If that was the case my above solution would have worked. You have to hide the original date column and not the new calculated max date column. Without original date column, the aggregation is based on the orderno and you get to see the max date for a specific orderno.

However if you show the original date, then max date column will aggregate based on both Orderno & Original Date columns and the results would not be as expected.

I tried with graphical calc views but no luck. I am thinking SQL calc views are the only way to achieve this.

Thanks,

Anooj

[Former Member]

Hi:

Can I use the same solution to calculate the max date in an analytical view if the order field is NVARCHAR?

Oder(Data type: NVARCHAR)                     Date(Data type: NVARCHAR)                     

1A                                                             10132012

1A                                                             10242012

1A                                                             10132012

1A                                                             10192012

2B                                                             10132012

2B                                                             10182012         

I want this result

Order             maxdate

1A                10242012

2B                10182012

Thanks                 

[Former Member]

Hi Luis,

Yes I think you should be able to. The max. date calculated measured should not have any dependency on the data type of Order.

Thanks,

Anooj

[henrique_pinto]

What you want as Output is a join between your original data and Anooj's solution from above.

[Former Member]

Thanks Anooj and Patrick.

I actually got it to work using graphical last week but I accidently deleted my calculation view and I can't seem to do it again.

I'm starting off with simple calcuaiotn with dates:

daysbetween(date("GLTRP"),now())

GLTRP is not in DAT format hence have to do a conversion.

But when I run the data preview I get the error (see below). Even without the date conversion I'm getting the same error. I tried all combinations, still same error. I tried both dataypes DATE and INTEGER in expression editor, same error. Output in attribute or measure.,.same thing. Can anyone help and tell me how I can do this in editor please??

[Former Member]

Hi Mic,

Are you doing this within a calculated measure? If so, do you want to try selecting the option 'Calculate before aggregation'?

Thanks,

Anooj

[patrickbachmann]

I had the same need as you and I solved it for myself by using SQLScript view instead of Graphical view and the SQL is similar to this;

SELECT

DAYS_BETWEEN (TO_DATE("BUDAT"), CURRENT_DATE) as "DAYS",

FROM

"YOUR_SCHEMA_NAME_HERE"."YOUR_TABLE_NAME"

In this example simply replace "BUDAT" with whatever field name you are using.  Then define an OUTPUT field on the right side, in my example I called it "DAYS".  I also have examples of multiple cases if you need that ie: if you want to have different buckets such as 30 DAYS in bucket A, 60 DAYS in bucket B etc.