Author Topic: Date Range Expression  (Read 83 times)

Offline kpagel

  • Associate
  • **
  • Join Date: Jun 2019
  • Posts: 2
  • Forum Citizenship: +0/-0
Date Range Expression
« on: 11 Jun 2019 03:51:22 pm »
I have created a Query Calculation to total ([dms].[inv].[tot_net_amt]). However, I only want it to total that value if [dms].[inv].[trans_date] is during a given calendar year (i.e., 2018, 2019, etc.). What is the best approach to create this expression?

Offline kpagel

  • Associate
  • **
  • Join Date: Jun 2019
  • Posts: 2
  • Forum Citizenship: +0/-0
Date Range Expression
« Reply #1 on: 11 Jun 2019 03:51:40 pm »
I have created a Query Calculation to total ([dms].[inv].[tot_net_amt]). However, I only want it to total that value if [dms].[inv].[trans_date] is during a given calendar year (i.e., 2018, 2019, etc.). What is the best approach to create this expression?

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,809
  • Forum Citizenship: +261/-1
    • Paul's Cognos Blog
Re: Date Range Expression
« Reply #2 on: 11 Jun 2019 11:27:09 pm »
easiest way would be to do total(case when [dms].[inv].[trans_date]  in (2018, 2019) then [dms].[inv].[tot_net_amt] else 0 end )

 



       
Twittear