Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor III

## Average Monthly Aggregate

Hi All,

I have a real basic QV App whereby I have a dimension table of Stock control, like below:

KeyLocationDateInDateOut
110101-01-2018 01:0002-01-2018 01:00
210101-02-2018 06:5520-02-2018 05:00
310202-01-2018 00:0003-01-2018 00:00
410313-03-2018 06:0015-03-2018 07:00
510102-02-2018 23:0028-02-2018 23:00

Then a Fact table:

KeyDwell Days
11
219
31
42
526

I then have a MasterCal based on DateIn.

What I expect using this expression is to get an average dwell per month =Aggr(Avg([Dwell]),[MonthYear])

With my expected results being:

MonthYearAvgDwell
Jan 20181
Feb 201822.5
Mar 20182

What I am getting however is the same result per month:

MonthYearAvgDwell
Jan 20189.8
Feb 20189.8
Mar 20189.8

Can someone point me in the right direction?!

Thanks

1 Solution

Accepted Solutions
MVP

I guess the problem is that you don't have your master calendar connected to your fact table

Since MonthYear is not connected to the main table....Avg([Dwell in Location]) is same across MonthYear... In the file that you provided what is DateIn and what is DateOut? Is t_put - DateIn and Next Location Put - DateOut?

Also, what if DateIn and and DateOut span multiple months? Is that something which can happen?

16 Replies
MVP

Are you using Interval Match with your DateIn and DateOut table?

IntervalMatch

Master II

Why do you even need Aggr here if [Month year ] is the only  dimension

Learning never stops.
Contributor III
Author

Sorry, yes I am. It is then joined back into the SC table.

Contributor III
Author

Sorry Pradosh, You are correct in that i don't need it, i still get the same answer. I think as Sunny eluded to, this could be because I am using interval match.

MVP

What are you interval matching on from your second table's point of view? In other words, which field does DateIn and DateOut matched to?

Master II

stalwar1‌ how did you know he is using interval match ?

Learning never stops.
MVP

How else can you join two dates in a single table to one date in another table

Contributor III
Author

Hi Sunny,

I created a bridge whereby I created a Key using key & datein.

Contributor III
Author

Just a note: I'm not slow to reply, my comments need to be moderated first!

Community Browser