Average Monthly Aggregate

[c_latham]

Hi All,

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

Key	Location	DateIn	DateOut
1	101	01-01-2018 01:00	02-01-2018 01:00
2	101	01-02-2018 06:55	20-02-2018 05:00
3	102	02-01-2018 00:00	03-01-2018 00:00
4	103	13-03-2018 06:00	15-03-2018 07:00
5	101	02-02-2018 23:00	28-02-2018 23:00

Then a Fact table:

Key	Dwell Days
1	1
2	19
3	1
4	2
5	26

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:

MonthYear	AvgDwell
Jan 2018	1
Feb 2018	22.5
Mar 2018	2

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

MonthYear	AvgDwell
Jan 2018	9.8
Feb 2018	9.8
Mar 2018	9.8

Can someone point me in the right direction?!

Thanks

[sunny_talwar]

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

IntervalMatch

[pradosh_thakur]

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

[c_latham]

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

[c_latham]

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.

[sunny_talwar]

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?

[pradosh_thakur]

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

[sunny_talwar]

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

[c_latham]

Hi Sunny,

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

[c_latham]

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