Total not works

[peewan]

I set up the Total in many difference way, but it didn't work right.
Any helps? Thanks a lot

EFF_DT = minimum ([EFF_DT] for [CAN_DT],[ID])

CAN_DT = when [CAN_DT] to_date('0001-01-01','YYYY-MM-DD') then 2020-01-01

Enroll = _days_between (CAN_DT,EFF_DT), and any days before 2018, will not count

Total (Enroll for [ID])

1. NOT WORK - Tried a new sub query, add new data item Total (Enroll for [ID]),
   Detail aggregation: default/calcutated
   Summary aggregation: default
3. NOT WORK Tried a new sub query, add new data item Total (Enroll for [ID]),
   Detail aggregation: calculated
   Summary aggregation: calculated
4. NOT WORK Tried a new sub query, add new data item Total (Enroll for [ID]),
   Detail aggregation: sumary
   Summary aggregation: default
5. NOT WORK Tried add new data item Total (Enroll for [ID]),
   Detail aggregation: default
   Summary aggregation: default


ID   Effective Date     Cancel Date      Enroll Days      Total Enroll Days
AD   Jan 1, 2017       Jan 1, 2018      0                  617
AD   Jan 1, 2018       Jan 1, 2019      0                  617
AD   Jan 1, 2019       Jul 1, 2019      181           617
AD   May 28, 2019     Jan 1, 2020      218           617
BE   Jan 7, 2018       Sep 1, 2018      0                   735
BE   Sep 1, 2018       Jan 6, 2019      5                   735
BE   Oct 25, 2018       Jan 1, 2020      365           735

[Francis aka khayman]

show us what the correct data should look like

[peewan]

correct data should be:

AD: 181+218 = 399, right now showed 617

BE: 395+5 = 400, showed 735

Thank you

[dougp]

Have you looked at the SQL that Cognos is producing to see what it is really asking the database server?

[peewan]

Have you looked at the SQL that Cognos is producing to see what it is really asking the database server?

How can i do that? thanks a lot

[bus_pass_man]

If you are using a relatively new (i.e. within the last half decade) report studio you edit the report, click on the cogwheel on the top right and choose show generated sql/mdx

If you are using a really old report studio you need to invoke a menu to get at that but I don't remember it.   

What is the values of the aggregation and usage properties of your query items?

I wonder if you have wondered why the values you got came about.   The values below suggests a pattern.

181   1  181      
218    2 436   
            -----
            617

5     1   5
365   2 730
           ------
             735      

If you have an ID where there's 3 or more records with values, does it result in a value for total enroll days similar to this pattern?

v1  1   1*v1
v2  2   2*v2
v3  3   3*v3
           -------
I think one thing to investigate is that something is aggregating because it has a usage of measure rather than one which would be appropriate for the object given its role, either identifier or attribute.   No matter what, you will probably find that it is doing exactly what you've told it to do.

Another thing to consider is the possibility that your enroll expression, which you don't provide in full, might be wrong.
      

[Francis aka khayman]

good observation. as doug suggested, better examine the sql generated. most probably some issue with the joins