I am working on a data where I have to calculate average sales for days of the week (i.e Only Monday's Average, Only Tuesday's Average ...)
Our Data source is Cognos 10.2
To achieve the above requirement I calculated "Day of the week" by using below mentioned syntax and successful in getting the results 1,2.....)
_day_of_week ([Time Metrics].[Transaction Date],1)
Now, my next step would be to write a syntax to display 1=Monday, 2= Tuesday, 7 = Sunday)
I tried using the below mentioned Syntax which was not successful:
I used the below mentioned syntax but getting an error message "An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'."
_day_of_week ([Time Metrics].[Transaction Date],1)=1
I am clueless, and this is my first real foray into Cognos queries so the syntax is fairly new to me.
My ultimate target is to calculate Average of only Monday's , Only Tuesdays and so on....
It would of great help if any one help me to write syntax to display 1 = Monday and calculate average of only Monday's.
Looking forward for the positive response!