average price

Fethi ZERARA

Hello guys, i'd like to know what should i do to get the average price of a certain amount of articles in a dataview? because i'm getting every time the sum of all the prices.

Thank you!

Andrea Florio

go to the layout of the dataview, enter the Configure to Block and in the Summary Algorithm choose 'AVG'.

Fethi ZERARA

Yeah i did it but it's not working!

Andrea Florio

hi Fethi Zerara

How many entity do you have in axes ?

Fethi ZERARA

I've got five entites Andrea Florio !

Andrea Florio

Fethi Zerara can you put only one entity by row? 

Fethi ZERARA

i need time entity product and society so i have to use a least three entities .

Andrea Florio

you can use a data view with time entity in column and product in row. Fethi Zerara Put in screen a selector with entity society. 

Fethi ZERARA

Not working , well what i'm trying to do is to get an average price of an article category , but here i'm getting the sum of  the prices of all the articles ang when i try to put AVG in summray the dataview doesn't change!

Previous Member

Could you show your layout (screenshot) ?

Fethi ZERARA

Excuse me , my work is in french , so what is underlined with blue is Quantity,by red is The price , and by yellow is sales revenues.

so here i'd like to have the average price of the quantity without using an algorithm!

Previous Member

Thanks.

By the way I asked for the layout, not the dataview itself.

Is the price information (blue underline) stored in a cube ? if yes, which entities structure the cube ?  can you show which options are applied to its block in the layout ?

so here i'd like to have the average price of the quantity without using an algorithm!

for which reasons do you want to avoid an algorithm ?

Fethi ZERARA

No the price is underlined in red. and it's stored in a cube whcich contains 5 entites (Year,Soceity, Commercial,Article,Client)

 

i don't what to use algorithm because i may change this data and manipulate it so i want it in a cube!

Previous Member

in this case i would try:

• block h: your cube, with option "Hide this block" active
• new block: Column algorithm
  • = h
  • summary algorithm = AVG

Fethi ZERARA

Not working !

Giving me the same result

Previous Member

Is your dataview using the flattened mode ? if yes, have you tried without it ?

Fethi ZERARA

still unsolved

Previous Member

Ok.

What happens if you start from scratch ? I mean, create a new screen, create 1 dataview with only 1 block for the cube PV au Mille € Brut (budget). Only set the summary algo = AVG and do nothing else (no selection, no refer to)

Fethi ZERARA

Well it works on down totals ,  but me i wanna get the information from the different levels of my hierarchy!

Previous Member

me i wanna get the information from the different levels of my hierarchy!

do you mean, you will see aggregated prices by product, which has no sense ?

Since your price information is in a cube, I see two options:

1. always show the cube data at the lowest level in the dataview to avoid aggregations: Year, product, company, Salesman, Customer have to be by row, or by column, or selected by pagers and selectors (or user security)
2. if 1. is not possible and you have aggregation, you cannot use this cube. You need to use the Quantity and Amount cubes which determine the price, and use a column algorith calculating the price on the fly. This would work regardless of you aggregation level.

Andrea Masiero

Hello. 

I know the context and the correct answer is exactly this. Using a column algorithm, you are assured the avg price is always correctly calculated, because the BOARD Dataview sum up the quantity and the values and then on these aggregated values the column algorithm perform his calculation. 

The cubes storing the avg price is useful in dataflows and in other procedures, but the information is showed correctly only at the lowest level of detail, otherwise you sum up prices, which is not correct. 

 

here how the layout is correctly configured 

 

 

column algorithm in c = d/b*1000