Set analysis question on sum of dimension column across fact record

[#1]

Hi, all.

I have "fact" data that looks like this:

Fact Key	Dimension Key	Start_Time	End_Time
1	1	4/1/2014 00:01:00	4/1/2014 00:01:45
2	1	4/1/2014 00:01:30	4/1/2014 00:01:55
3	2	4/1/2014 00:01:37	4/1/2014 00:02:00
4	3	4/1/2014 00:01:42	4/1/2014 00:02:13

And, dimensional data that looks like this:

Dimension Key	Event	Total	Attempts	Success
1	Login	1	0	0
1	Authenticate	3	2	1
1	Pay Bill	1	0	0
2	Login	1	0	0
3	Login	1	0	0
3	Authenticate	2	1	1
3	Check Balance	1	0	0

I'm trying to create a chart with a straight table that shows me the total sum for each event, narrowed down by time and time zone selections.

I've brought the total dimension into my straight table, and have applied this expression:

SUM({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}>} event_total)

However, the values I get are less than what I'm expecting, and don't balance with a table viewer object I'm using for comparison purposes.  I think my set analysis is missing a link to the fact table, but I'm not sure.

What am I missing from my set, or might I have a breakage upstream from this work?

Thanks!

[jagan]

Hi,

Attach sample file and expected output.

Regards,

Jagan.

[#3 · Accepted solution]

Hi, all.

The solution was to sum and aggregate between fact and dimension:

SUM(AGGR(SUM({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}>} event_total), Fact Key, Event))

Thanks!