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
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