Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Would you be able to share your script?
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?
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!
Did you look at the thread I shared earlier?
I would suggest you to go through the thread and see if this is sort of what you are looking to do?
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?
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....
You mean on the master calendar part?