Aggregation Aggravation

[cfountain72]

Hello,

I am trying to aggregate a set of hourly values by date/hour within a pivot table..Anyone have any suggestions on how I can make this:

look like this:

The bottom version was achieved by doing the accumulations in the load script, but that means users can't apply filters to exclude/include certain portions based on their dimension types. The expression being used in the top version is:

sum(aggr(sum([AdmitDurations.AdmitHoldIntervalDuration]), [AdmitDurations.ActiveDate], [AdmitDurations.ActiveHour], [AdmitDurations.User_ID]))*24.

Can this be modified to make it perform the accumulation in the table?

Thanks in advance for your help,

Chris

Ps. this example just shows one ID, but the actual data set comprises of 10's of thousands of ID's, totaling millions of individual duration measures.

[Anil_Babu_Samineni]

@cfountain72 Perhaps this way?

Rangesum(Aggr(Below(Sum([AdmitDurations.AdmitHoldIntervalDuration])*24,0),Above([AdmitDurations.AdmitHoldIntervalDuration],1,RowNo(TOTAL)),[AdmitDurations.ActiveDate], [AdmitDurations.ActiveHour], [AdmitDurations.User_ID]))

[LRuCelver]

What exactly do you want to achieve?

Do you want to accumulate the sum for the entire day? (Hour 10 = sum of all values from hour 0 to 10)

[cfountain72]

Thanks for your question. Yes, but continuing on to the next day(s). I would like it to accumulate AdmitHoldIntervalDuration for as long as the ID is active. So if if you look at the example, the values start in the 1900 hour on 3/4, and continue to accumulate durations throughout the next day (3/5).

[cfountain72]

Thanks for your help Anil. I tried that expression, but it yielded an empty pivot table.

[Anil_Babu_Samineni]

Do you wish to share sample file to test with? 

[cfountain72]

Thanks Anil,

Here you go... 

ID	AdmitHoldIntervalDuration (minutes)	Date	Hour
190847936	0	03/04/2024	10
190847936	0	03/04/2024	11
190847936	0	03/04/2024	12
190847936	0	03/04/2024	13
190847936	0	03/04/2024	14
190847936	0	03/04/2024	15
190847936	0	03/04/2024	16
190847936	0	03/04/2024	17
190847936	0	03/04/2024	18
190847936	0	03/04/2024	10
190847936	0	03/04/2024	18
190847936	4	03/04/2024	19
190847936	60	03/04/2024	20
190847936	28	03/04/2024	21
190847936	32	03/04/2024	21
190847936	60	03/04/2024	22
190847936	60	03/04/2024	23
190847936	60	03/05/2024	0
190847936	60	03/05/2024	1
190847936	60	03/05/2024	2
190847936	60	03/05/2024	3
190847936	60	03/05/2024	4
190847936	60	03/05/2024	5
190847936	49	03/05/2024	6
190847936	11	03/05/2024	6
190847936	60	03/05/2024	7
190847936	60	03/05/2024	8
190847936	60	03/05/2024	9
190847936	60	03/05/2024	10
190847936	60	03/05/2024	11
190847936	60	03/05/2024	12
190847936	60	03/05/2024	13
190847936	60	03/05/2024	14
190847936	60	03/05/2024	15
190847936	60	03/05/2024	16
190847936	60	03/05/2024	17
190847936	60	03/05/2024	18
190847936	60	03/05/2024	19
190847936	60	03/05/2024	20
190847936	60	03/05/2024	21
190847936	60	03/05/2024	22
190847936	59	03/05/2024	23