Current date is not working in Data Module

[Kiran Kandavalli]

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

[bus_pass_man]

I tried that just now and it works for me. 

What is the error message?   That would be helpful.

[MFGF]

Also, what build of Cognos Analytics are you using? Release 6?

MF.

[Kiran Kandavalli]

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.

[MFGF]

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.

[Kiran Kandavalli]

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 Expand



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

[MFGF]

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 Expand



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.