Time Filtering

rexmanu
rexmanu
in Modeling

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!

 

rexmanu_0-1665089218730.png

 

Tagged:

Best Answer

  • Misbah
    Misbah
    Answer ✓

    @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

    Misbah_0-1665119208659.png

    Thanks,

    Misbah

    Miz Logix

     

Answers

  • JaredDolich
    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:

    JaredDolich_0-1665093987427.png

     

  • rexmanu
    rexmanu
    Thanks Jared! I appreciate your insight and contributions to the community.
  • rexmanu
    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!

Categories