How to force a sort for calculations?

[hespora]

Hi there,


I'm trying to create a calculation that will separate my sales filtered for product and time into quartiles based on qty sold, and then to get me the highest price in the lowest quartile and the lowest price in the highest quartile. Now unfortunately, as my sales on line level include a quantity and thus it is a weighted sample, I cannot use Cognos' own quartile() or quantile() functions (unless I am misunderstanding how they work). So I'm building that manually.

To do that, I've done these steps:

- calculate [price] per line as [Sales]/[qty].
- use the pre-sort option of the price item to sort ascending
- calculate [total qty] as total [qty] for report. both aggregates set to cal.
- calculate [running-total qty] as running-total [qty] for report
- calculate [quartile] as ceiling ([running total qty] * (1 / 0.25)/[total qty])
(all of these calculations have aggregate and rollup aggregate function set to calculated)

up to here, this will populate [quartile] with a value from 1 to 4, depending on where in the qty range the price is sitting. HOWEVER, it seems to only do this in the display, not in the query itself. Meaning: If I use [quartile] to do any further calculations, cognos ignores the sorting and assigns a different value for that data item (due to the usage of running-total, the above way to calculate does indeed yield different results based on how the result set is sorted)

I am at a loss here what to do. How do I force cognos to observe my sorting?


/edit: to make it a little clearer, I've attached a mockup of what I want as a screenshot. On top is my raw data, below are the calculations. The green stuff works fine; the yellow stuff is what is dependent on sorting, (and I've just tried, running-total even changes the result when a different layout sorting is applied), and the red columns are what currently yields the same false result no matter what I do, indicating that internally, cognos uses one unchangeable way to sort my result set.

[Lynn]

I have often found quartile and quantile difficult to work with when you want to do something further based on the resulting quartile/quantile value because of just what you describe - it comes up with a different answer when other things are brought into the mix. No amount of fudging with aggregate settings solves this for me, although I strongly suspect that wiser minds might crack it.

To get around this, I find that using a query reference such that query1 gets the necessary quartile figure and then Query2 references Query1 and goes on to do more with it. The attached spec illustrates this. Of course with small volumes of data a query reference isn't hugely problematic but I do believe it relies on local processing so the normal caveats apply depending on your situation.

Sorry this isn't directly addressing your question but perhaps it is an option to consider.

[hespora]

That was my original idea, too. I always thought that using a child query or query reference, or whatever its official name is would be like: execute query A, then take that result set as a table, and execute query B from that table.

Unfortunately, that doesn't quite seem to be what is happening. If I do this and compare the quantile numbers for the very same billing line for query A (pre sorted on price) and query B (not sorted), I'm getting different results!  >:(

By just playing around, I am a little step further now though. Going into Query A's properties and setting Auto Group and Summarize to No, my further calculations based off the quantiles yield the correct results - on the 4k rows that this query has, and I'm failing to condense that down, again thinking query reference, due to the same kind of problem described above.

[hespora]

Anyone got an idea on this? I'm not really getting any further. :(

I've looked into every statistics definition of how to calculate weighted quantiles I could find, and they all rely on an empirical distribution function which in the end is just another running-total calculation, so I really need to be able to force a sorting to be observed.

[BigChris]

I'm not a fan of this, but could you construct the query in SQL and drop that into an SQL reference?

[hespora]

Good question... I'll look into that, although last I've used straight up SQL was 15 years ago (not counting little hacks here and there), and I'm afraid what our model creates is fairly complex. :)

[Lynn]

I'm also not a fan of SQL in reports, but if you are going to go down that road two thoughts come to mind:

1) You can also consider a stored procedure query subject. Someone would need to produce it within the database and then the Cognos Framework model would need to include it and publish it for reporting. These can be one-trick pony sources, capable of producing a very specific report and not much else.

2) For the SQL in the query approach, it can sometimes help to start by grabbing the SQL from the report you've already got and then amending further rather than starting from scratch. I realize that isn't an earth-shattering revelation but I mention it in case it hadn't crossed your mind  :D

As a last-ditch option for your existing approach, perhaps you could mock up a similar report using the great outdoors samples. That might help someone else play around to see if they can find a way to solve the problem.

[hespora]

I've escalated to the cognos gurus within my org for now, hoping they will have some idea what to do. I've looked into the SQL, and indeed, I can see that there are several instances of an "ORDER BY" clause populated by my price calculation in Query 1, and those same clauses are populated with a static value of 100 in Query 2. I've pointed that out to them as a possible source.

About the sample package: God, I wish. I've asked for that at least four times in the past 3 years since my org switched from BO to Cognos that I want that, exactly for purposes like these. It used to be on some test server, now inaccessible, and it's not published on any other instances, and nobody except me thinks it's a good idea to do so. :(

[bdbits]

For what it's worth, I avoid SQL in reports like the plague. But, I once a handful of very messy HR reports and ended up adding some report-specific query subjects that were hand-coded SQL to get what was needed. I kept them off in a folder by themselves so it was clearly isolated. Hated to do it, but on rare occasions will succumb to it. Just don't make it a habit.

On the samples, find a reason to submit a problem to IBM (maybe this one), who will almost certainly request that you recreate the problem with the samples. Take that request to your admins and tell them they have to install the samples.  8)

Personally, I think it is rather silly of them not to put them on a test server (assuming you still have at least one non-production server).

[hespora]

Soooo, they finally gave me access to the sample packages, so I've built the whole thing in go sales (query).

To recap (so nobody has to read the entire thread again):
Query1 runs the actual calculations; what I ultimately want are the [max price...] and [min price...] query subjects. To calculate these correctly, I have to include every individual order line in the query, and I have to sort on the calculated order line price. Also, query1 *only* yields the correct results if "auto group & summarize" in the properties of the query is set to "no".

Once I have values for my query subjects, I only want one line with them. So I'm feeding Query1 as a reference into Query2. As you can see in that report, query2 does not yield the same results for the [max price] and [min price] query subjects. I need it to.

I'll gladly hear any thoughts one of you may have on this. :)