How to Get the Description of Maximum Values

[markcognos]

Hi,

I have a problem to get the description of maximum values : Here is the example :

Weather |CountWather     |Maximum|MaxValue
Sunny     | 3                        | 3           |? (Must Sunny)
Sunny     | 3                        | 3           |? (Must Sunny)
Sunny     | 3                        | 3           |? (Must Sunny)
Rainny    | 2                        | 3           | ? (Must Sunny)
Rainny    | 2                        | 3           | ? (Must Sunny)

in Weather Count :
Sunny is 3
Rainny is 2.

So ill get the maximum of weather count which is 3.

How can i get the weather descripton Sunny of the maximum of 3? the value in MaxValue must Sunny.
Can anyone help?Pls...

I Need this output:

Weather |CountWather     |Maximum|MaxValue
Sunny     | 3                        | 3           |Sunny
Sunny     | 3                        | 3           |Sunny
Sunny     | 3                        | 3           |Sunny
Rainny    | 2                        | 3           |Sunny
Rainny    | 2                        | 3           |Sunny

[velniaszs]

Forget Cognos that just does not make sense.

So what you are saying that if tomorrow weather forecast is rainy, but maximum of weather of last Mondays was 3 it should be sunny tomorrow? Why?
Are you deriving [Weather] data item?
Please send the report xml file.

[markcognos]

Hi,

This report is a 7 days forecast..But the data of weather description is per seconds, so let says that there are 3600 seconds in 1 day, so their is 3600 weather description for the sunny and rainy, so i only need to get the maximum weather description for the day, assuming that the maximum is sunny for the day, so need to get the sunny description only..ill need only to get the maximum count description per seconds,if you put the weather description in crosstab example for day 1,it shows 2 weather description which is sunny and rainy.. So i need only to show one description for the maximum count of a weather in crosstab..

I need to do this because no mode function in report studio.

The table above is just an example,but the real data for this is per seconds per day.

Thanks,

[markcognos]

Hi,

I only want to know what function that need to use to get the weather description of maximum values.

Thanks,

[CognosPaul]

So, if I understand, your table looks something like:

DayKey
SecondKey
WeatherKey

With standard SQL, you are correct in that there is no mode function. However there are still plenty of workarounds. The normal way would be to use a subselect to count and rank the occurrences, then filter where rank=1. Which database are you using? Some of them have statistical extensions which include mode, others have UDFs.

I feel, however, that this is more of a modeling challenge. Why not do the logic in the ETL?

[markcognos]

Hi CognosPaul

I don't have access in ETL or in database side, some group are incharge for that, i'll only have an access only in report studio.

Any functions that may use for my scenario?Or any formula?

Thanks,

[CognosPaul]

What is your database? Also, how much data do you have?  My experience with weather data is in the  millions of rows per day and trillions of rows in the table.  Is that consistent with your setup?

[markcognos]

Hi,

They using a DB2 database, for now the data is loaded only 1 day for testing in test server..Their are several meeting of how many months or days may load in production server. For now, we only use a test server for the creation and testing of reports..The production server is ongoing working with other team.

Thanks

[CognosPaul]

DB2 is a bit more difficult. Do you know which version? Do you have any of the new optimizations hardware?

How often is the data loaded? How many rows?

With large data sets some planning is needed to make sure the queries actually run and meet the user needs. Will the users need to see today's data, or only historical?

Which version of Cognos, can you set up a dynamic cube?

The easiest way to do it is to create a new query.
Day
Weather
Counter: count(weather)
Rank: rank (counter for day)

filter:
rank = 1 (after auto aggregation)

[markcognos]

Hi Cognos Paul

we used cognos 10.2,and the model that we used is DMR model,

In your solution in creating a new query, i need also to create a relationship in my mainquery Subject?

Or it is posible to do it in only my mainquery not to create a new query subject because it affects a report performance,because we need to create also a relatoinship with that new query subject.

IN my report i want only to get the sunny description only in my data item(maxValue)- the value sunny in my report,This is another column and must dynamic,not to create a filter,because i need also to show the rainy in my deatiled report


Any suggestion how to do it only in my mainquery? and get only the maximum description in dynamic formula? Any formula?
instead of creating a new query subject.

Thanks,

[CognosPaul]

Mark,

The reason I keep asking about the size of your data set is to judge the performance hit you might get from different techniques. Again, I've worked with weather data before and I need to know certain things before I can help you. A solution for a small data set would probably freeze your Cognos or db server if you had trillions of rows. A table constantly adding thousands of rows each second (especially on older version of DB2) will not work well with table scans.

How big is your data set? Do you have partitions or indexes on a day/site level? Is this a pure DMR? Are you running on DQM or CQM? Can you tell me more about your report? Will you be displaying more than one day at a time, more than one site at a time?

As you said, a new query would require a relationship. With a DMR environment this will not be possible.

Mode is a difficult calculation to do, especially in dimensional environments. I think your best bet would be to push this as close to the DB as possible. You mentioned you can't touch the database, but can you make changes to the model?

I do have another idea. How many different weather types do you have? Do you really have 1 row for each second, or is that aggregated at all?

[markcognos]

Hi CognosPaul,

1.)How big is your data set?
   - For now we are using a test server,some groups handle for the live server, and no final decision how many data that they load in that server,several meeting has been on going in the client.

2.)Do you have partitions or indexes on a day/site level?
   - Yes.they said they have an index in the tables, i can't see it because don't have access in database.

3.)Is this a pure DMR?
   - Yes, Pure DRM Model

4.)Are you running on DQM or CQM?
   - i am running in publish package came from framework manager

5.)Can you tell me more about your report?
   - The table above is just a sample, the true repoort for this is i used a crosstab,then days is 7 days forecast.I first to get the weather description on a list for me if i get the maxvalue of weather description,it is easly for me to transfer it in crosstab..when i put the weather description in crosstab per day it shows many description,because the data is per hours per minute,per seconds.

6.)Will you be displaying more than one day at a time, more than one site at a time?

   - I have 2 reports...1 is 7 days forecast which is displaying only 7 days i use a date prompt to do this report..2 is hourly forecast which is only shows the data only in one day then shows all hours. All is aggregated

7.)but can you make changes to the model?

   - I can't touch the model, i have no access to it, some groups handle the model.Only reports Studio i have an access.

8.)How many different weather types do you have?

   - We have 4 different weather types,.The weather description is possible to increase depends on the client requirements.

Thanks,

[CognosPaul]

Okay, I'm beginning to get the picture.  Does your model have any sort of row counter as a measure? Would it be possible to request one be added?

Getting the count of weather types will be difficult. If you create a crosstab with seconds, with weather nesting inside, in the rows, will you get 1 row per weather/second, or 4?

My idea is to explicitly count each weather for each day - basically, convert each weather into a measure and use an if statement to decide which label to render. This is possible as there are so few weather types.

Let's try this.

Build a crosstab with a date you're familiar with. Drag the date into the rows.

Next, let's try creating a Sunny measure. tuple([Sunny],[RowCount]) If you don't have a RowCount measure, create a new data item in the query with: member(1,'one','one')
Drag that into the columns and run the report. Do you get the correct number of sunny seconds?