Skip to Content
0

Show last 4 quarters of data in webi report

Jan 03, 2017 at 12:13 PM

144

avatar image

Hi Experts,

I have a webi report where I am getting 2 years(PY and CY) of data and my requirement is to show last 4 quarters of data in table.

Supplier Q1 Q2 Q3 Q4

S1

S2

S3

S4

Quarters should be in such a way that it should show last 4 quarters only e.g. if we are in 2nd quarter of 2017 then

Supplier Q2-2016 Q3-2016 Q4-2016 Q1-2017

S1

S2

S3

S4

Table should show 4 quarters data in dynamic way.

Please guide.

Thanks and Regards,

Ankit

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

garima goyal Apr 04, 2017 at 11:50 AM
0

hi,

we can do this as per my approach:-

First, Create a variable to calculate last year current quarter.

Step 1.Variable for Last Year Current Quarter = Var1

=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])+1))=1) Then Concatenation([py];".Q1")

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])+1))=2) Then Concatenation([py];".Q2")

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])+1))=3) Then Concatenation([py];".Q3")

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])+1))=4) Then Concatenation([py];".Q4")

Note:-PY is previous year

Step 2.Create a Flag for displaying/hiding Values

=If([Year Qtr] >= [Var1] ) Then "Show" Else "Noshow" -------- Apply this Flag as Show in the main report

Step 3.Create a Flag for hiding Current Qtr

=If([Year] =[Current Year] And [Fiscal Qtr] = [Current Quarter]) Then 1 Else 0 ----------- Apply this Flag as 0 in the main report

Now lets take an example of current date i.e.4 april,2017. So,it should show last 4 quarters Q2-2016 Q3-2016 Q4-2016 Q1-2017

According to var1's Relative Date condition,date will be 1 april,2017 and it will lie in 2nd quarter so the second condition will become true.

It will give the output:- 2016.Q2

The second step will give you all the quarters which are greater than equal to 2016.Q2,the output will be:-

2016.Q2

2016.Q3

2016.Q4

2017.Q1

2017.Q2

now as per Step 3 ,we need to remove Q2 of 2017,for that Create a Flag for hiding Current Qtr and apply it as 0 in the report.

u will get the desired result:-

2016.Q2

2016.Q3

2016.Q4

2017.Q1

Thanks,

Garima Goyal

Share
10 |10000 characters needed characters left characters exceeded