Week data of month in dashboard

[Former Member]

Dear Experts,

We created the universe in IDT based on HANA calculation view, need to show current week data and next 3 weeks data (coming weeks) of month dashboard.

Requirements is like below...

Current week + Amount

Current week + 2nd week

Current week + 3rd week

Current week + 4th week

Current month of current year and rest of month of current year

Is it possible to define the above requirements in IDT data foundation layer by using Derived table and calculated columns or we need to do it in HANA calculation itself

Can anyone help me on this requirement, we need to resolve it ASAP.

Many thanks in advance!!

[former_member183330 · Accepted solution]

Hi Ravi,

Create Week object in IDT based on date like below:

[WEEK]=week([Date])

in where clause

(week([Date])Between week(Curdate()) And week(Curdate())+3)

And (Year([Date])=Year(Curdate()))

If you use this week object you will get current week + next 3 weeks also.

Regards,

G Sampath Kumar

[Former Member · Accepted solution]

Dear Sam,

Thanks for the logic.

[Former Member]

Hi Ravi,

You can do the same on WEBI only by creating separate queries for current week data,2nd,3rd & 4th week data.

After getting all the data from all the queries you can merge the comman dimensiones & get your desired output.

Thanks,

Swapnil

[Former Member]

Hi Swapnil,

Thanks for the response..

It is specific to business point of view so we should do it only IDT Data Foundation layer.

[Former Member]

Then in that case just create a derived table with query like below

select 'current month' , all the columns required by you....

where date in current_month...

union

select '1st week',all the colunns required by you..

where date in 1st week
union

select '2nd week',all the colunns required by you..

where date in 1st week

like wise...

where you have to apply filter conditions based on date objects ...

I hope you understand that I mean..

Thanks,

Swapnil

[Former Member]

I am not familiar with derived table syntax, could you provide the complete logic.

[Former Member]

check below video it will show you how to create derived table.

https://www.youtube.com/watch?v=ZZMktZ_QQOU

If you want me to create it then please provide your table metadata & also some sample data.

Thanks,

Swapnil