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: 
Not applicable

Displaying avg prices

Hi,

I have a pivot table in which I have to display the PY Prices for each material and month and the PY Price at material level without considering the month. (see the attachment for a good understanding).

The problem is when I calculate the PY Price for each material, it compares only in one cell, but I'd like to repeat the same value for all months.

My formula is

avg(aggr(PY_PRICE,,MG_DESCRIPTION_MG_LEV1, MAT_MATERIAL_CODE)).

How to modify it in order to repeat the value in all the other cells?

Thank you

1 Solution

Accepted Solutions
Carlos_Reyes
Partner - Specialist
Partner - Specialist

Then just add the Sum to the PY_PRICE and also add the TOTAL qualifier in the AVG. Try:

avg( TOTAL <MAT_MATERIAL_CODE>

     aggr(

          Sum(PY_PRICE)

     ,MG_DESCRIPTION_MG_LEV1, MAT_MATERIAL_CODE)

)


Perhaps you also need to add the MONTH_MONTH_OF_YEAR field to the aggregate.

View solution in original post

15 Replies
Carlos_Reyes
Partner - Specialist
Partner - Specialist

Right off the bat, I think your formula has two many commas before the first aggregation field. It should be:


avg(

     aggr(

          PY_PRICE

     ,MG_DESCRIPTION_MG_LEV1, MAT_MATERIAL_CODE)

)

Not applicable
Author

Oh, no...it was a typo...my mistake. In QV the formula has only one comma.

It isn't this the reason of the malfunction

Carlos_Reyes
Partner - Specialist
Partner - Specialist

Then just add the Sum to the PY_PRICE and also add the TOTAL qualifier in the AVG. Try:

avg( TOTAL <MAT_MATERIAL_CODE>

     aggr(

          Sum(PY_PRICE)

     ,MG_DESCRIPTION_MG_LEV1, MAT_MATERIAL_CODE)

)


Perhaps you also need to add the MONTH_MONTH_OF_YEAR field to the aggregate.

Not applicable
Author

Dear Carlos Alberto Reyes Díaz,

I've realized I have one problem using this formula. Considering my data is split up by month, it works perfectly when I show up all the months, but when I click on one single month the data change, which is not correct.

Do you have any idea why it does this bad association?

Thanks

Cristina

Carlos_Reyes
Partner - Specialist
Partner - Specialist

Hi Cristina

That issue is due to QlikView's default behaviour. Let me be more specific. When you have no selections in Month, or any other field that could reduce the number of available months information, QlikView calculates the formula based on the available or possible information. However, when you filter something that results on only one month of information, QlikView only has the information related to that month to calculate the formula. This way, QlikView will always calculate the formula based on the available or possible information given the current selections.

This is the default behavior, nonetheless you can disregard selections in order to make sure that your formula always takes all possible months of information. You can use Set Analysis in order to do this. Try:

avg(  {$<MONTH_MONTH_OF_YEAR= >} TOTAL <MAT_MATERIAL_CODE>

     aggr(

          Sum(  {$<MONTH_MONTH_OF_YEAR= >}  PY_PRICE)

     ,MG_DESCRIPTION_MG_LEV1, MAT_MATERIAL_CODE)

)


You may also want to check a similar post that will help you undestand this:

Sales person vs top/bottom sales person per month

Also there are very good posts about how to play with rolling dates scenarios:

Calculating rolling n-period totals, averages or other aggregations

Hope it helps!

Not applicable
Author


Thank for your reply, but I would like the user to be able to analyze the data for each month, but also to keep the average price calculated of all 12 months.

Doesn't exist a way to do it?

Cristina

Carlos_Reyes
Partner - Specialist
Partner - Specialist

Well, it depends on how exactly you want to achieve that. However as far as I know you can't do both things in the same expression in a different way than the first answer I gave you.

If you upload a document (qvw) with an example it'll be easier to help you.

Not applicable
Author


Hi,

You'll find the enclosed application. I hope you can open it because every time I tried to upload an application nobody couldn't open it.

Let me know if you have this problem.

Thanks

Carlos_Reyes
Partner - Specialist
Partner - Specialist

Cristina,

It's strange but I cannot open your file either. Perhaps you're using Section Access?