Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
c_latham
Contributor III
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
sunny_talwar

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

Capture.PNG

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?

View solution in original post

16 Replies
sunny_talwar

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

IntervalMatch

pradosh_thakur
Master II
Master II

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

Capture.PNG

Learning never stops.
c_latham
Contributor III
Contributor III
Author

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

c_latham
Contributor III
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.

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
Master II
Master II

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

Learning never stops.
sunny_talwar

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

c_latham
Contributor III
Contributor III
Author

Hi Sunny,

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

c_latham
Contributor III
Contributor III
Author

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