WEBI - Year to Date on column chart?

[Former Member]

I have a WEBI report pointing to UNX

Fields -

Year [2015,2015 etc]

Month [01,02...12]]

Amount [12, 34 ..77]

Product [A,B,..Z]

I have a cross tab showing amount for each product by month.

I created a column Year to Date and applied SUM(Amount) and this works.

Issue is I have a column chart which shows same data of amount by each month.

I want to show YTD as column on chart after 'december' month??

                 Jan | Feb |       Dec| YTD

Product|  

[Former Member]

this is what I am looking for

[Former Member]

Hi Vishal,

If you have only one yer of data then you can try this

Create a dummy object in universe with definition "YTD"

create a union query in your report

select Year,Month,sum(Amount) from table1 group by Year,Month

union

select year,Dummyobject,amount from table1 Year

this query gives the out put like below

2015 jan 1000

2015 Feb 1000

-

-

2015 jan 1000

2015 YTD 12000

and you can use this result for creating ur column chart

[Former Member]

Divya,

I have multi year data but I am fetching 1 year at a time.

Also you mean I should create a dummy dimension YTD type string and keep it  SQL selection blank in universe?

[former_member206070]

Hi Vishal,

Yes, you are creating the dummy dimension in universe level only.

Once up on a time i was facing this type of issue. This is the solution for my issue. It is just like same as your issue. please  use this formula depends on your requirement.

YTD

Hiredate>=

Case when to_char(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'),'mm') in ('01','02','03')
then

to_date('1/4/'||to_char(Add_months(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'),-12),'YYYY'),'DD/MM/YY')

else

to_date('1/4/'||to_char(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'),'YYYY'),'DD/MM/YY')

end

and

Hiredate<=

LAST_DAY(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'))

Regards,

Ravi.

[Former Member]

Ravi , this formula where do I place?

Also I dont have date prompt.

I have Month & year as separate fields

[Former Member]

Yes Ravi,

You should create dummy Object in the universe with a string as its definition "YTD" and point to the table from which you are getting the actual data and parse it.

then create a combined query whose query looks like the way i mentioned in my previous post.

[former_member206070]

Hi vishal & Divya,

Sorry for the delay, Vishal i believe your problem was clear right now.

(yes Divya after creating the Object we will do like that only)

Regards,

Ravi.

[Former Member]

Hi Ravi,

I Have similar issue i tried but no luck .can you explain the formula how you achieved

to_date('1/4/'||to_char(Add_months(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'),-12),'YYYY'),'DD/MM/YY')--> Why you mentioned 1/4 here?

Case when to_char(to_date(@Prompt('enter date','D',,mono,free,),'DD-MM-YY hh24:mi:ss'),'mm') in ('01','02','03')--> Why u mentioned in(01,02,03)

Could you please brief me

TIA

Varun