Author Topic: Current date is not working in Data Module  (Read 2620 times)

Offline Kiran Kandavalli

  • Community Leader
  • *****
  • Join Date: Jun 2011
  • Posts: 109
  • Forum Citizenship: +1/-0
Current date is not working in Data Module
« on: 15 May 2017 04:28:08 pm »
Team,

Can anyone help me out with the below calculation.

I have a measure named Sales. I need to calculate Current year Sales & Last Year, Current Month, Last Month Sales.

1) I have Date Dim which has YEAR_NUMBER field.
2) Sales Field is in my Fact Table.

3) For Current Year Calculation I have used in the Data Module as below
 
     if(GO_TIME_DIM.YEAR_NUMBER = _year(current_date)) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

4) Similarly for other Calculations Last Year Current Month, Last Month Sales.

5) in the Data Module it shows as Valid and when I pulls those in the Dashboard it is taking ages to run and it is throwing running time error.

6) I thought it is performance issue, but when I pull the Over All Sales it is working fine / or when I hard code the Current year Sales it is working fine.

Hard coded the Current Year and it is working fine

 if(GO_TIME_DIM.YEAR_NUMBER = 2017) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

Thanks!
Kiran
« Last Edit: 16 May 2017 11:05:51 am by Kiran Kandavalli »

Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 384
  • Forum Citizenship: +41/-0
Re: Current date is not working in Data Module
« Reply #1 on: 17 May 2017 05:25:59 am »
I tried that just now and it works for me. 

What is the error message?   That would be helpful.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,003
  • Forum Citizenship: +653/-10
  • Cognos Software Muppet
Re: Current date is not working in Data Module
« Reply #2 on: 17 May 2017 08:54:42 am »
Also, what build of Cognos Analytics are you using? Release 6?

MF.
Meep!

Offline Kiran Kandavalli

  • Community Leader
  • *****
  • Join Date: Jun 2011
  • Posts: 109
  • Forum Citizenship: +1/-0
Re: Current date is not working in Data Module
« Reply #3 on: 17 May 2017 10:26:56 am »
I am using 11.0.5 version.

I tried by connecting Cognos to these two databases Netezza & Denodo.

It's not throwing me an error in Data Module. But when I pull the Current year Sales Column in the dashboard it is throwing me run time error.

I thought it is because of Data - but my assumption is wrong, because when I hardcode to 2017 it is working fine but when I use _year(current_date) it is throwing run time error in the Dashboard.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,003
  • Forum Citizenship: +653/-10
  • Cognos Software Muppet
Re: Current date is not working in Data Module
« Reply #4 on: 18 May 2017 04:13:55 am »
if(GO_TIME_DIM.YEAR_NUMBER = _year(current_date)) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

Hi,

_year() isn't an inbuilt Cognos function, but current_date() is. Try using a consistent mix of function types, eg

if(GO_TIME_DIM.YEAR_NUMBER = extract(year(current_date))) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

Does this fix the problem?

Cheers!

MF.
Meep!

Offline Kiran Kandavalli

  • Community Leader
  • *****
  • Join Date: Jun 2011
  • Posts: 109
  • Forum Citizenship: +1/-0
Re: Current date is not working in Data Module
« Reply #5 on: 18 May 2017 11:27:39 am »
Hi,

Thanks for your response!

I tried using extract as below (Syntax 1 & 2) but it is still not working.

I thought Syntax 2 is throwing Run time Error because of Joins, so I have used DATE_KEY from the Fact table, so that it will join with any of the table. (Syntax 3) - but still it is not working

Code: [Select]

syntax 1 - The expression is not Valid

if(GO_TIME_DIM.YEAR_NUMBER = extract(year(current_date))) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

syntax 2- The expression is Valid, but it is taking ages to run in the Dashboard

if(GO_TIME_DIM.YEAR_NUMBER = extract(year,current_date)) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

syntax 3 - The expression is Valid, but it is taking ages to run in the Dashboard

if( substring( SLS_SALES_FACT.DATE_KEY,1,4) = substring(current_date,1,4)) then ( SLS_SALES_FACT.SALE_TOTAL) else (0)


Thanks!
Kiran
« Last Edit: 18 May 2017 11:37:41 am by Kiran Kandavalli »

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,003
  • Forum Citizenship: +653/-10
  • Cognos Software Muppet
Re: Current date is not working in Data Module
« Reply #6 on: 19 May 2017 03:30:15 am »
Hi,

Thanks for your response!

I tried using extract as below (Syntax 1 & 2) but it is still not working.

I thought Syntax 2 is throwing Run time Error because of Joins, so I have used DATE_KEY from the Fact table, so that it will join with any of the table. (Syntax 3) - but still it is not working

Code: [Select]

syntax 1 - The expression is not Valid

if(GO_TIME_DIM.YEAR_NUMBER = extract(year(current_date))) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

syntax 2- The expression is Valid, but it is taking ages to run in the Dashboard

if(GO_TIME_DIM.YEAR_NUMBER = extract(year,current_date)) then (SLS_SALES_FACT.SALE_TOTAL) else (0)

syntax 3 - The expression is Valid, but it is taking ages to run in the Dashboard

if( substring( SLS_SALES_FACT.DATE_KEY,1,4) = substring(current_date,1,4)) then ( SLS_SALES_FACT.SALE_TOTAL) else (0)


Thanks!
Kiran

Hmmm. Nothing further I can suggest here, sorry. It's worth logging this one with IBM to see what they say?

Cheers!

MF.
Meep!

 



       
Twittear