Calculating Average for Day of the week - Cognos 10.2

Hello Experts,

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!

Thanks,
Ganesh
Ganesh VijaykumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
Please provide some sample data and what the expected results should be.
0
Ganesh VijaykumarAuthor Commented:
Hi Awking00,

I have attached the sample data for your reference!

I have done all the calculations in Excel ( Total 4 calculations)

As mentioned in the post i am able to achieve calculation 1 and hit a roadblock to achieve the rest.

I am pretty new to cognos and query syntax.. I am sure there is way to calculate Average only for Monday's...

Looking forward for your help!

Thanks,
G
0
awking00Commented:
Attachment?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ganesh VijaykumarAuthor Commented:
Oops sorry....Here you go!
Avg-only-for-Monday-s.xlsx
0
Ganesh VijaykumarAuthor Commented:
Hi Awking00,

I am able to achieve results for Calculation 2 with below mentioned syntax.

If ([Day]='1') then ('Monday') else (' ').

In this case i would have 7 columns to calculate Only Mon's Tue's..... Sun's and 7 columns to calculate averages respectively.

Now i will start working on calculating averages.

Please let me know if we can have Mon, Tues,..... Sunday in one column and calculate average.

Thanks,
G
0
awking00Commented:
The following query produces these results from your sample data -
DAY              AVG
--------- ----------
Sunday        6432.5
Monday       4676.67
Tuesday       8163.5
Wednesday       3780
Thursday     5573.83
Friday          4224
Saturday           0

with cte as
(select level as d, decode(level,1,'Sunday',2,'Monday',3,'Tuesday',4,'Wednesday',5,'Thursday',6,'Friday',7,'Saturday') day
 from dual
 connect by level <= 7)
select cte.day, round(coalesce(x.amt,0)/coalesce(x.cnt,1),2) as avg
from cte
left join
 (select to_char(tdate,'d') d, to_char(tdate,'Day') day, sum(amt) amt, count(*) cnt
  from sales
  group by to_char(tdate,'d'), to_char(tdate,'Day')
 ) x
 on cte.d = x.d
order by cte.d;

If you just want the average for a specific day just replace the "order by cte.d" with "where cte.day = 'DayOfWeek' (e.g.'Monday')"
0
awking00Commented:
Also, if you don't care about days that are not in the data, you can simplify the above to -
select day, round(amt/cnt,2) as avg from
(select to_char(tdate,'d') d, to_char(tdate,'Day') day, sum(amt) amt, count(*) cnt
 from sales
 where to_char(tdate,'Day') =  'DayOfWeek'
 group by to_char(tdate,'d'), to_char(tdate,'Day')
);

This query would produce no rows selected is 'Saturday' was entered in the where clause.
0
Ganesh VijaykumarAuthor Commented:
Good Day!

Thank you very much for the quick response!

As i mentioned earlier I am very new to the query syntax. I am not sure how to update the above mentioned query in "Query Calculation' under Cognos 10.2

I have calculated two columns:

one named "Day" using syntax _day_of_week ([Time Metrics].[Transaction Date],1) to display 1=Monday, 2= Tuesday,..... 7 = Sunday)

Second named "Only Mon" using syntax If ([Day]='1') then ('Monday') else (' ') to display only Monday's in the column.

Data items in our Database
I have attached the screen shot of actual column names for your reference.

Request you help me updating the query.

And also please help me understand how it will be displayed, I am curious to know how it will looks once i run the query:

1) As there will be lot of Monday's, Tuesdays's based on Transaction date.. will the data look like below

DAY              AVG
--------- ----------
Sunday        6432.5
Monday       4676.67
Tuesday       8163.5
Wednesday       3780
Thursday     5573.83
Friday          4224
Saturday           0
Monday       4676.67
Sunday        6432.5
Thursday     5573.83
 
OR

DAY              AVG
--------- ----------
Sunday        6432.5
Monday       4676.67
Tuesday       8163.5
Wednesday       3780
Thursday     5573.83
Friday          4224
Saturday           0

2) Will the query create two column "Day" and "Avg"

Thanks again for all your help, I am very new to cognos and may be i am asking stupid questions.

Your help in this regard will deeply appreciated!

Thanks,
G
0
awking00Commented:
I'm sorry, but I know practically nothing about Cognos. What I provided was a query with Oracle syntax that gets the information you're seeking. The query is reasonably complex in that it has a number of sub-queries and employs many of Oracle's built-in functions (e.g.to_char, to_date, coalesce, etc.). How it can be translated using "Query Calculation" under Cognos, I have no idea. Since the issue appears to be, "How can I do this in Cognos?" and not an Oracle problem, I might suggest requesting attention to a moderator to expand the search to include Cognos, specifically, and perhaps Business Intelligence as well. Good luck.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ganesh VijaykumarAuthor Commented:
My Bad... That's Okay.. Not a problem!

I will request for attention to a moderator to expand the search to include Cognos, and Business Intelligence.

Thanks,
G
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.