topic Re: Average Monthly Aggregate in QlikView

Average Monthly Aggregate

c_latham — Sat, 07 Apr 2018 20:32:09 GMT

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

Re: Average Monthly Aggregate

sunny_talwar — Sun, 08 Apr 2018 18:19:04 GMT

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

IntervalMatch

Re: Average Monthly Aggregate

pradosh_thakur — Sun, 08 Apr 2018 19:14:11 GMT

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

Re: Average Monthly Aggregate

c_latham — Mon, 09 Apr 2018 09:16:43 GMT

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

Re: Average Monthly Aggregate

c_latham — Mon, 09 Apr 2018 09:21:51 GMT

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.

Re: Average Monthly Aggregate

sunny_talwar — Mon, 09 Apr 2018 17:40:55 GMT

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?

Re: Average Monthly Aggregate

pradosh_thakur — Mon, 09 Apr 2018 17:43:49 GMT

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

Re: Average Monthly Aggregate

sunny_talwar — Mon, 09 Apr 2018 17:45:26 GMT

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

Re: Average Monthly Aggregate

c_latham — Tue, 10 Apr 2018 11:27:40 GMT

Hi Sunny,

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

Re: Average Monthly Aggregate

c_latham — Tue, 10 Apr 2018 12:58:20 GMT

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

Re: Average Monthly Aggregate

sunny_talwar — Tue, 10 Apr 2018 15:06:03 GMT

Would you be able to share your script?

Re: Average Monthly Aggregate

sunny_talwar — Tue, 10 Apr 2018 19:36:13 GMT

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?

Re: Average Monthly Aggregate

c_latham — Tue, 10 Apr 2018 19:44:25 GMT

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!

Re: Average Monthly Aggregate

sunny_talwar — Tue, 10 Apr 2018 19:45:55 GMT

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?

Re: Average Monthly Aggregate

c_latham — Tue, 10 Apr 2018 19:53:14 GMT

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?

Re: Average Monthly Aggregate

sunny_talwar — Tue, 10 Apr 2018 19:54:58 GMT

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....

Re: Average Monthly Aggregate

c_latham — Tue, 10 Apr 2018 19:57:28 GMT

‌You mean on the master calendar part?