WebI report Previous Quarter from Calendar Quarter field

[former_member648413]

Hi All, Need quick help in calculating previous quarter from a calendar quarter field within the report.

Calendar quarter = 2020-Q1 ; 2020-Q2 ; 2020-Q3 and so on.

Users wants to filter on "Previous Quarter" i.e. Current Quarter - 1

Let me know if there is a function within WEBI or how to calculate on reporting end without a predefined filter in Universe.

Thanks,

[ayman_salem · Accepted solution]

You can get the quarter with Quarter(date).

ex.

prevQ: =Quarter(CurrentDate()) - 1

then you can put in your formula like

searchQ: ="2020-Q" + [prevQ]

..

of course, you need to be clear about whether the first quarter is 1 then the previous Quarter is Q4 from last year.

...

I hope this gives you an idea how to do it.

[former_member648413 · Accepted solution]

Hi Ayman,

Thanks for responding quick. However, How to get Q4 if it is Q1? I get Q0. Should I define an IF statement first ?

[ayman_salem · Accepted solution]

it is so simple:

curQ: =Quarter(CurrentDate())

prevQ: if (curQ=1) then 4 else curQ-1

and do the same for "year" using also the curQ in if statement

[former_member648413 · Accepted solution]

Thank you.

Now I have the new variable SearchQ and Calendar Quarter column. I need to filter on SearchQ variable on the data end. Where to do filterto filter only on SearchQ Result?

[ayman_salem · Accepted solution]

add variable:

status: if ([Calendar quarter] = [SearchQ]) then 1 else 0

then filter your table with "status equal to 1"

[former_member648413 · Accepted solution]

Thanks a lot. It helped. 🙂

[former_member648413 · Accepted solution]

Hi Ayman,

If I have Month in the report and wanted to schedule the report on a monthly basis automatically. How do I do that on report end without pre-defined filter in the universe ?

Month = January . For previous month, do we need to write a if then else statement to specify the number first ? COuld you please explain?

Thanks a lot.

[ayman_salem · Accepted solution]

use Month(CurrentDate()) instead of Quarter(CurrentDate())

and MonthNumberOfYear(CurrentDate()) to get the month number.