Two different time periods on single crosstab

[taprice]

I'm creating a report that consists of a single cross tab. On this report I am attempting to summarize the number of calls per day for a given month as well as the number of calls for the previous 5 weeks.

I attempted to use 2 cross tabs: one for the daily and another for the rolling 5 weeks, but I could not get the rows to match.

My report should look something like:

Call Topic	Day 1	Day 2	MTD Total	Week 1	Week 2	Week 3	Week 4	Week 5
Complaint	1	2	3	4	2	2	1	3
Compliment	1	2	3	4	2	2	1	3

I am currently attempting to use a join of two queries in a single crosstab. The first query basically pulls the information by date, the count of the calls, and the topic. The second pulls the information by week, the count of calls, and the topic.  I use the topics as the rows, the days and weeks as the columns and one of the counts for the measure. The numbers are not matching and it looks like a cross join is occurring.

Does anyone have any suggestions on how to approach this problem?

[Cognerd]

Assuming your data is non-dimensional, you'll probably need to do a union of your two results so you can add them as a single column as peers.

[kmuller]

I had a similar situation where I needed to compare current year and prior year (selected from a prompt) and get the variance between them.  To do it, I created a CY query, and then a PY query.  I then unioned them togehter and figured the value for each year, and then compared them to get a variance.  I displayed the info on both Crosstabs and list queries.  It is not too hard.  Let me know if you need more details.

Kevin

[Veekaygee]

Kevin has hit it right on the head. I have also had to make a cross tab with 5 different periods and I used the same technique. It works really cool.

[taprice]

Thank you for the replies, but i'm having difficulty implementing this. Just to grasp the concept, I'm trying something simpler.

If I create a query that pulls 2 things: Item Name and Date item sold.  With this I can group by item and do a count to find how many items I sold on a particular day.  Now I apply a filter to this query and grab everything from the start of the month onwards. I than duplicate this query and change the filter to the second time period i need (same number of days from previous month). So I have 2 queries that pull the same data but for 2 different time periods.

Now I have a union query with 2 data items. If I were to create a list to show the different time periods... how do I create the two different Date columns from the single column showing the 2 different periods? I can get it to show the previous month's info after it has shown the current month's info... but I can't figure out how to show that info in two separate columns.

[keviv]

Hi Kevin,

I need one clarification from you regarding the 'union' which you mentioned in your post.
According to what i understood from the post, we can create 2 separate queries (CY and PY) and union them to get a single Unioned query. Now we have the required data items in the final 'unioned' query where a single data item displays value for the measure (CY,PY) at runtime.

When we drag and drop the particular item in the 'unioned' query into the crosstab, it'll display the values for the CY and PY as two columns in the crosstab at RUNTIME.

But, you have mentioned that you could find the variance of the two values(CY and PY) and display it in the crosstab.

Can you please clarify on how you could calculate the variance of two values which are getting generated at runtime from a single data item in the unioned query.

I have this requirement in my project. Any help in this regard will be highly appreciated. :)

Thanks in advance,
Vivek

[MFGF]

Hi,

how do I create the two different Date columns from the single column showing the 2 different periods? I can get it to show the previous month's info after it has shown the current month's info... but I can't figure out how to show that info in two separate columns.

Instead of using a UNION between the two queries, use a join instead, and add a join condition based on the Item Name with a cardinality of 1..1 -> 1..1

This should allow you to report the counts from this month and last month in separate columns on the same row.

Best regards,

MF.

[larsonr]

Actually the best solution is to design relative time. 

What does this mean?

Basically based on a specific time period, you can obtain other information in relation to this time period.

Example,  you have to be able to grab the current period, the previous period, the same period last year and the previous period last year.  What we do is designed this relational dimensional modeling to take an input of sysdate and generate these numbers for any data item joined to it.

Check out proven practices under support on cognos's website.  Under Modeling --> Design there are two good documents for creating this relative time that you can use and abuse to your advantage to build the query you mentioned at the beginning of the post.