Extracting all months prior to current month

[wdwiz4]

Hi everyone!

I'm currently stuck on a user request that I can't figure out. The user is requesting to see only data prior to the current month because they can't report current month data. I've tried using extract(month, current_date) but keep getting an error.

Ideally, I would like to use this as a filter so that it brings back only the months prior to the current month.

Thanks in advance!

[hespora]

On relational, that would be:

Code Select Expand


[date]

<=

_last_of_month (

  cast(

    if
      (_month(current_date) = 1)
    then
      (_year(current_date)-1)
    else
      (_year(current_date))

    + '-' +

    if
      (char_length(

        cast(

          if
            (_month(current_date) = 1)
          then
            (12)
          else
            (_month(current_date)-1)

        , varchar(2))

      )=1)
    then
      ('0')
    else
      (null)

    +

    if
      (_month(current_date) = 1)
    then
      (12)
    else
      (_month(current_date)-1)

    +'-05' , date)

/edit: yes, I'm quite sure that is ugly and probably not as efficient as it can be, but it works. ;)

[Lynn]

Is your data source relational? Are you filtering on a date or do you have a month/year field of some sort?


If your source is relational and you want to filter so that only dates prior to the current month are returned you can simply use this as your filter:

Code Select Expand


[Your Date] < _first_of_month ( current_date )


[hespora]

or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)

[AnalyticsWithJay]

or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)

Don't let that discourage you from helping. We all come from different backgrounds, so seeing other people's approach to problems is always good -- even if it's an approach you learn to avoid.

No shame at all :) There's no true mastery of Cognos (it's too damn big!)

[Lynn]

Don't let that discourage you from helping. We all come from different backgrounds, so seeing other people's approach to problems is always good -- even if it's an approach you learn to avoid.

No shame at all :) There's no true mastery of Cognos (it's too damn big!)

Absolutely true! Plus things are always changing. I learn a lot here and appreciate the input from so many different people facing a wide variety of requirements and situations.

[wdwiz4]

Thanks everyone! I will try this out and report back with my results.

Thanks again!

[MFGF]

or that, obviously... I'm gonna go sit in a corner now and have my colleagues yell "shame" at me repeatedly. ;)

You didn't tell me you were going to adopt my role! Whatever will I do now? Sigh. I will be demoted from shame-boy to tea-boy, no doubt. :)

MF.