HANA Views

[former_member298186]

Hello Colleagues,

I would like to know if someone could help me out here.

We have a table containing financial written-off amounts, i.e. from bills for which the company will never receive payments.

Lets say the table looks like this:

CLIENT	CompCode	Date	Amount	Currency
090	0001	20140120	10	EUR
090	0001	20140123	20	EUR
090	0001	20140201	40	EUR
090	0001	20140203	20	EUR
090	0001	20140205	30	EUR

I want to produce the sum amounts grouped by week numbers, i.e. from the above data I would like to create a View (Calculation/Analytical...) which returns the values 30 (first 2 dates belong to week 4), 40 (week 5) and 50 (week 6).

I thought that I could perhaps produce use a calculated column to convert the 'Date' values to calendar week integers (similar to select week(date) from myTable ) but the week function is not one that is available in the 'Expression Editor' within the view creation module.

The goal is to produce a chart displaying the week numbers with columns representing the written-off sums for each week.

We have already done this using an OData Service (implementing the GET_ENTITYSET method of the generated class) and a DDL view in the ABAP layer and then programming a SAPUI5 application to display the data.

We would now like to see how much effort is involved in producing the same application via the KPI Modeler and Smart Business applications.

Any insight would be helpful.

Kind regards,

Keith Sanderson

Amount

[Former Member · Accepted solution]

Create calculated column for week and in expression editor use function ISOWEEK(<datecolumn>).

Although ISOWEEK function is not listed there but it works. This will give you YYYY-WNN output.

I.e. 2014-W04

Regards,

Gourav

[former_member182302 · Accepted solution]

Adding to Gourav's comments:

rightstr(ISOWEEK(<datecolumn>),2) is what you would need for your formula.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

I don't recommend to remove year from week as if you have more than 1 year data then you don't know whether it is from this year or last year.

In graph you can still sort and display it correctly using ISO format i.e. 2014-W04.

Regards,

Gourav

[Former Member]

Hi Keith,

One solution is generating time data(Quick launch window) and creating an Attribute View(Sub type: Time) with the required Calender type and Granularity. Then, joining this attribute view with the data foundation(having your table) in an Analytic View.

-Latha.

[former_member298186]

Thanks Latha, I'll give that a try.

Regards,

Keith

[former_member298186]

Hi Latha,

I've tried numerous combinations but I can't seem to create a view to return the data that I need.

For example, I thought I would need to create an Attribute view based on the M_TIME_DIMENSION_WEEK table BUT then noticed that I can't join this to my table as I only have a date column, i.e. at DAY granularity.

Therefore I created an attribute view based on the M_TIME_DIMENSION table to match the  granularity of the Date column in my table and joined this to my foundation table using the SAPDATE column..

The M_TIME_DIMENSION table also has a WEEK column and I thought I would be able to use that as a basis for the aggregation (SUM) of my Amount column but I haven't been able to make that work.

So basically I have a table like this:

MANDT	NVARCHAR
COMPANYCODE	NVARCHAR
BUSINESSPARTNER	NVARCHAR
AMOUNT	DECIMAL
CURRENCY	NVARCHAR
POSTINGDATE	NVARCHAR
...	...

And I want to return sums of the amount field based on the WEEK number of the POSTINGDATE field

The annoying thing is that I can write a simple SQL statement like this to produce what I want:

select  week(postingdate), sum(amount)

from myDbTable

where year(postingdate) = 2014

and companycode = '123'

and currency = 'EUR'

and mandt = '090'

group by week(postingdate)

The problem is reproducing the 'week' function in an analytic/calculation/attribute view.

Of course 'companycode', 'currency' etc would need to be dimensions but I'm sure you understand what I mean.

Any tips/ideas would be very welcome.

Kind regards,

Keith

[Former Member]

Hi Keith,

Here is what I did and was able to get the output as you expected.

1. Created an Attribute view with time data generated with granularity @ Week.

2. Created a sample table and the data as follows:

3. Created a Calculation View joining these two tables and then adding Amount as aggregated column in Aggregation node and mentioned Week as an attribute in semantics node. The data preview of the Calculation view is as below:

Hope this helps. Reach out if you need further assistance.

Best Regards,

Latha.

[former_member298186]

Hi Latha,

I've just performed the following steps:

Create Attribute view of the M_TIME_DIMENSION_WEEK table...

And then I created a Calculation View using this Attribute View, joining it to my table. Here is the Join view...

Here is the Aggregation view...

And here is the Semantic view...

I hope that these are legible when you click on them.

However, I'm still not receiving any raw data when I test the view.

There must be something basic that I'm doing wrong?`

To be honest I cannot see how the join can work as the fields are of different granularity, If I write the join in SQL I also receive no result.

Thanks once again for your help.

Kind regards,

Keith

[Former Member]

Hi Keith,

Firstly, after generating the time data(Quick Launch window), create an attribute view with subtype Time and specify the granularity and Calendar type. Just activate this as it is and you should see the data in Data Preview. No need of modeling this explicitly.

Secondly, I have joined the time attribute view generated above(not the M_time_dimension_week table) with the Week-amount( DFKKWOH) table.

Make sure you are joining the date column of your table with date column(DATE_SQL) of the time attribute view. Following is a screen shot of the view I created:

-Latha.

[Former Member]

Hi Keith,

In the attribute view you selected only WEEK filed. You need to select YEAR field as well to join the view with your source table/view.

Krishna.

[former_member182302]

You are joining a date field with Week. Hence you are not getting any data in the output.

Regards,

Krishna Tangudu