Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@cfountain72 Perhaps this way?
Rangesum(Aggr(Below(Sum([AdmitDurations.AdmitHoldIntervalDuration])*24,
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)
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).
Thanks for your help Anil. I tried that expression, but it yielded an empty pivot table.
Do you wish to share sample file to test with?
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 |