Distinct SUM 1

[MDXer]

I am fairly new to Cognos, currently moving from MS Analysis Services.

I have a report that I have been told to create with impromptu, which has been faily easy except for one Measure that I have to report. This measre is a sum of values in one column based on the distinct values in another column.

Example

Row_ID Order_ID Geust_Count Item_ID
--------- --------- -------------- ---------------
1 1001 3 9867
1 1001 3 8765
1 1001 3 2345
2 1002 2 1234
3 1003 1 3456

Measures being reported are Count of Order_id which I accomplished using Count ( Distinct Order_ID) which using the data above returs 3 which is correct. The place where I am stumped is total guest count, which should be 6 because it is a sum of guest count based upon distinct order_id.

Is this even possible in cognos? Any help would be greatly appreciated.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Recce]

Hi,MDXer

I could suggest that you Group 'Order_ID' and the sum it in a different calc, if I understand correctly ?

 

[GilMerc]

Hi MDXer,

As Recce said, you must group your list on Order_ID and you create a calculation field where the definition is count(line) for Order_ID.

After this you can move a result in any part of your report.

Gilles.

 

[MDXer]

Thanks for your input but I'm not sure either of these will work.

Recce's Idea I have tried and what ends up happening is that the result is the sum of all so what you end up with would be 12 and not 6.

GilMerc's suggestion will result in a count of rows which in the result above would be 5.


At the leaf level a formula such as SUM(GuestCount)/Count(OrderID) would work, but only at the most granular of levels where you would be looking at a single order.

A couple Cognos vetrans in our company said this was possible so the fact table was built in this manner. I'm starting to think I may have to revisit the way the fact table is designed.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[GilMerc]

MdXer, I think I don't understand exactly what you want to do. Give me an example. I'm sure it's possible to use function of Impromptu. I use them frequently.

I think it's very important to use them without modified catalog as possible.


Gilles.

 

[MDXer]

GilMerc, I appreciate your help and will try to be more detailed in what I am trying to do. The data comes from a Food and Beverage POS System.

Checks - The list of items ordered for a Table or Group. Each check can me comprised of multiple line items. The check is represented by Check_ID.

Guest Count - This is the number of people the check represents or total people in the group. The Guest Count is the same for all line items associated with a check.

Item_ID: The Id of the menu item ordered.

Cost: Cost of the Menu Item.

example data

Row_ID Time     Check_ID   Geust_Count Item_ID  Cost
------ -------  ---------  ----------- -------  ------
1      10:00    1001       3           9867     $5.23
2      10:00    1001       3           8765     $3.54
3      10:00    1001       3           2345     $9.45
4      10:00    1002       2           1234     $25.23
5      11:00    1003       1           3456     $12.24
6      11:00    1004       2           3489     $8.50
7      11:00    1004       2           3539     $15.43
8      11:00    1005       1           3679     $10.50

The similar to my fact data. all relevant columns are shown.

With the above data I will be reporting an hourly break down of the data for a business day. Showing the Totals for the entire hour.

Check_Count - Distinct Count of check_ids

Total_Guests - Sum of Guest_Count for distinct check ID. In the data the Guest_Count is repeated for each line item, however it represents the total guests for the check so in the example above Check_id 101 has 3 Total Guests and 3 line items so a sum of guests would produce 9 and not 3.

Total Cost - Sum of the Cost Column.

The results of this report on the sample data would appear like the following. The report type is a Simple List report.

Expected Report Results

Time    Check_Count  Total_Guests  Total_Cost
----    -----------  ------------  -----------
10:00   2            5             $43.45
11:00   3            4             $46.67

I hope this better explains and illustrates the problem.

Again thank you for your help.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[GilMerc]

Hi MDXer,

First, you need to group your report by time.
Add calculation field name,check_count, where definition is count of DISTINCT Check_ID for Time.
Add two calculation fields name, Max_Guest and Total_Guest, where definition are Max_Guest = Maximum of Guest_Cout for TIME and CHECK_ID. Total_Guest = SUM of MAX_GUEST for TIME.
Add calculation field name, Total_Cost, where definition is SUM of COST ITEM for TIME.

Now set of the list : You will see 1 line per CHECK_ID (I suppose) to delete this you will take a footer of group TIME and put your data in this section (Time, Check_Count, Total_Guest and Total_Cost) and show footer of this group and hid a detail of the list.

Ouf....... I hope this solution will work for you. It's more difficult that I suppose in a first view. I hope it will help you.

Give me a news.
Gilles.

 

[MDXer]

Thanks I will let you know how it works.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[DoubleD]

There is another way to accomplish this, write the following calculation:
total(if (Item_Id = max(Item_Id)) then (Guest_Cnt) else (0))

 

[MDXer]

Thanks to everyone for their help. For ease of use for the end user I as much as I didn't want to ended up bulding a table the was a summary of the lineitems. Had I been the primary architect on this datamart I probably would have used a different approach but my role is to make it functional for the end user. The reports that are based off this new table obviously fun in a fraction of the time they did when pinted at the line item table, and the over head to populate this order level table is less than 1 minute which considering some of the impromtu reports required 20 minutes to run I guess it is ultimately worth it.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MDXer]

I finally did figure the solution out to this although probably not in the most glamorous way. THe record contained a Line item number so I was able to add some logic to the summary value.

Total(If(lineitem = 1) Then (Guest Count) Else (0))

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck