Time Filtering

rexmanu

Hey all, I'm wanting to update a time filter to use in module views but am having a hard time figuring out how to create a specific condition. I want to have a filter for the current quarter and the previous five quarters as well (so 6 quarters total).

 

Right now I have a line item that gives each quarter a decimal value descending from the current quarter with this formula: IF Current Quarter?? THEN 1 ELSE NEXT('Trailing 6 Q Count') / 2 -- Then a line item boolean that has this formula: IF 'Trailing 6 Q Count' > 0.03 THEN TRUE ELSE FALSE

 

This allows me to filter the most recent 6 quarters (or any amount really), but I feel like there is a better way to write the formula. I've looked at other time functions like LAG, PREVIOUS, etc. but don't know how to make them return a range of periods rather than a single result.

 

Any help appreciated!

 

 

Misbah

@rexmanu 

 

Simplest way is

 

ITEM(Time) > 'Current Period' - 6 AND ITEM(Time) <= 'Current Period'

Note: Here Current Period is the line item and can be calculated as PERIOD(CURRENTPERIODEND())

          Current Period has to be updated on TIME page

          This module is dimensioned by Months but you can have it on Quarters

Thanks,

Misbah

Miz Logix

 

JaredDolich

@rexmanu 

Believe it or not you can just add 3 and subtract 6 from a PERIOD formatted line item. Use that on a Boolean. For example:

 

rexmanu

Thanks Jared! I appreciate your insight and contributions to the community.

rexmanu

Thanks Misbah! Worked like a charm. I adjusted the numbers to match the conditions a little bit but it works great. I didn't know you could write it this way. Thank you again for your insight and your contributions to the community!