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]

Would you be able to share your script?

[sunny_talwar · Accepted solution]

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?

[c_latham]

Not entirely certain I know how I would get the Master Cal to link to the Fact table. If I did a date(t_put), MM-YYYY on the fact table I would get a link?

Again you’re correct on your assumption of t_put and Next Location Put!

Yes that’s true stock can come in and stay in a location for months and not move, again not entirely certain I would know how to get this to display! Thanks Sunny!

[sunny_talwar]

Did you look at the thread I shared earlier?

IntervalMatch‌

I would suggest you to go through the thread and see if this is sort of what you are looking to do?

[c_latham]

Thats exactly what I need (And thought I had done!). I got my wires crossed! It’s been a good few years since I last used QV! Now I’ve got to the position I’m in, I think next steps are interval match and then bring the master calendar into the fact table! Do you agree?

[sunny_talwar]

I would keep them separate because bringing them together can cause an explosion of data... you wouldn't want that.. there will be a synthetic key... but that synthetic key is actually not bad....

[c_latham]

‌You mean on the master calendar part?