topic Problem by calculating the average in QlikView

Problem by calculating the average

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear Collegues,

I have an application in which I use three tables:

the first one i have all the production orders sent to our suppliers and its day and supplier

RELEASE_DAY	ORDER	SUPPLIER
01/01/2017	1	A
01/01/2017	2	A
01/01/2017	3	B

2. the second one i have the "queue" of all the suppliers in every day since today - 400 days ago

DAY	QUEUE	SUPPLIER
01/01/2017	2.4	A
01/01/2017	1.4	B

3. I have a master calendar. the purpose I created that calendar is to show in a graph all the days, instead of only the days that there was an order release.

How they are related:

The first table is related with the second one by DAY and SUPPLIER. The second one is related with the third one by the day only;

My goal of showing all the day was accomplished, but the thing is: I want to calculate the average queue of all suppliers in a certain day, but only I can do is calculate the average queue of the suppliers that had a realeased order in that day.

Can someone help me with this?

Re: Problem by calculating the average

swuehl — Sat, 09 Sep 2017 07:12:39 GMT

Not exactly sure what you are trying to achieve, so an expected result with the provided data would definitely help.

Maybe it's just about how you are calculating the average, so instead

=Avg(QUEUE)

you could try something like

=Sum(QUEUE) / Count(TOTAL DISTINCT SUPPLIER)

to calculate the average using all distinct suppliers across all dimensional (date) values.

Re: Problem by calculating the average

Anonymous — Mon, 11 Sep 2017 21:53:59 GMT

Stefan, you are right, I guess I wasnt specific enough.

Let´s suppose that my supplier´s base is the one below with it´s WIP on the day 01/01/2017

The average WIP (what I am trying to calculate) in that case is 1.4.

What the application is doing is taking into account only the Suppliers that had a realease date in the day Im willing to check.

In the day 01/01/2017, only the Suppliers A, B and E had an order released to them. For that reason, the application is returning the average as 1.5, instead of 1.4.

Thus, my question is: how to make it take the 5 suppliers into account, instead of only 3?

Thks!