- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Average Monthly Aggregate
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you using Interval Match with your DateIn and DateOut table?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Why do you even need Aggr here if [Month year ] is the only dimension
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, yes I am. It is then joined back into the SC table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
stalwar1 how did you know he is using interval match ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How else can you join two dates in a single table to one date in another table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
I created a bridge whereby I created a Key using key & datein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just a note: I'm not slow to reply, my comments need to be moderated first!
- « Previous Replies
-
- 1
- 2
- Next Replies »