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
I tried that just now and it works for me.
What is the error message? That would be helpful.
Also, what build of Cognos Analytics are you using? Release 6?
MF.
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.
Quote from: Kiran Kandavalli on 15 May 2017 04:28:08 PM
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.
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
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
Quote from: Kiran Kandavalli 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
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.