Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewaf
Creator
Creator

Average formula

Hi,

i have the following data set, lot of order line with different date.

I would like to have the average of column 3, regardless the fact that there are several line.

Basically i would like to select January and February and get as AVERAGE , 3 (2+4/ 2 MONTH).

Which is the right formula to get this result?
Thank you!

Cattura.PNG

25 Replies
anders_thorngaa
Contributor III
Contributor III

Hi Antonio,

I think the formula you are looking is:

=sum(DISTINCT Instruments)/count(DISTINCT myMonth)

In the load statement I have defined myMonth (as the month of the order date). Also see attached files.

Regards

Anders

qlikviewaf
Creator
Creator
Author

Hi,

this is working only if the value of instruments is different month-by-month.

If i set for February and March the same value equal to 4, the average needs to be 4, but appears 2 since the sum(DISTINCT Instruments) should work by month.


How i can i solve this?

ISSUE.PNG

anders_thorngaa
Contributor III
Contributor III

I think see the problem with the solution I described before.

If I understand you correctly, I suggest using the following formula instead:

=Avg(Aggr(Avg(DISTINCT Instruments),myMonth))

Regards,

Anders

qlikviewaf
Creator
Creator
Author

i reshaped with

=sum(aggr(sum(DISTINCT Instruments), myMonth))/count(DISTINCT myMonth)

Hafedh_BOUMESSOUER
Contributor
Contributor

Hi Antonio,

you should try ony this formula  : AVG( Column3)

Capture.PNG

2.PNG

qlikviewaf
Creator
Creator
Author

It's working but only if the customer is selected.

If i don't select the customer the sum(Distinct allocatedinsturment) goes to 0.

In the screenshot below, Allocated instruments is equal to = sum(Distinct AllocatedInstruments) / count(Distinct Month)

do you know why if i deselect the customer (ship-to), the value gos to 0? It's the sum(Distinct allocated Instruments) that goes to 0.

Thank you

ISSUE.PNG