Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set analysis question on sum of dimension column across fact record

Hi, all.

I have "fact" data that looks like this:

Fact KeyDimension KeyStart_TimeEnd_Time
114/1/2014 00:01:004/1/2014 00:01:45
214/1/2014 00:01:304/1/2014 00:01:55
324/1/2014 00:01:374/1/2014 00:02:00
434/1/2014 00:01:424/1/2014 00:02:13

And, dimensional data that looks like this:

Dimension KeyEventTotalAttemptsSuccess
1Login100
1Authenticate321
1Pay Bill100
2Login100
3Login100
3Authenticate211
3Check Balance100

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!

1 Solution

Accepted Solutions
Not applicable

Re: Set analysis question on sum of dimension column across fact record

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!

2 Replies
MVP & Luminary
MVP & Luminary

Re: Set analysis question on sum of dimension column across fact record

Hi,

Attach sample file and expected output.

Regards,

Jagan.

Not applicable

Re: Set analysis question on sum of dimension column across fact record

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!