topic Re: Displaying avg prices in QlikView

Displaying avg prices

Tue, 05 Nov 2013 21:07:30 GMT

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

Re: Displaying avg prices

Carlos_Reyes — Tue, 05 Nov 2013 21:30:16 GMT

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)

)

Re: Displaying avg prices

Tue, 05 Nov 2013 21:35:39 GMT

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

Re: Displaying avg prices

Carlos_Reyes — Tue, 05 Nov 2013 21:47:37 GMT

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.

Re: Displaying avg prices

Mon, 11 Nov 2013 09:24:45 GMT

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

Re: Displaying avg prices

Carlos_Reyes — Mon, 11 Nov 2013 15:49:13 GMT

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!

Re: Displaying avg prices

Mon, 11 Nov 2013 16:31:18 GMT

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

Re: Displaying avg prices

Carlos_Reyes — Mon, 11 Nov 2013 17:03:42 GMT

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.

Re: Re: Displaying avg prices

Mon, 11 Nov 2013 17:21:38 GMT

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

Re: Displaying avg prices

Carlos_Reyes — Mon, 11 Nov 2013 18:08:52 GMT

Cristina,

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

Re: Re: Displaying avg prices

Mon, 11 Nov 2013 18:58:16 GMT

I'm not using the Section Access.

I've just turned on the Admin Override Security from Document Properties, I don't know if this was the cause, but you can try again.

Re: Displaying avg prices

Carlos_Reyes — Mon, 11 Nov 2013 19:35:15 GMT

I still can't open your document. You say everyone has problems with your documents, have you tried to save the app in a different computer and upload it from that computer?

I have no idea of what can cause this problem.

Re: Re: Displaying avg prices

Mon, 11 Nov 2013 20:21:54 GMT

I haven't tried it yet. Could be some settings on the server? Because either my colleague who works on the same machine has the same problem when she tries to upload documents.

I've made a copy on my local and I've tried to uploaded from here. Try now, please!

Thank you very much for your availability.

Cristina

Re: Displaying avg prices

Carlos_Reyes — Mon, 11 Nov 2013 21:05:16 GMT

Nope.. I still can't open your document and I don't know what can be hapenning. I think you should ask your QlikView provider why do you have this problem. Perhaps QlikCommunity itself may help... you can open a thread to try to figure out what could be the problem. I remember that in old versions of QlikView there were some settings like security for MACHINE ID... perhaps you're using something like that.

Do you manage the QlikView environment? What kind of security are you using? What version of QlikView?

I'd love to help you resolve this but I don't have the slightest idea of what is causing this problem.

Sorry for not being able to help you.

Re: Displaying avg prices

Tue, 12 Nov 2013 15:46:36 GMT

Hi,

finally it works using the formula

avg( TOTAL <MAT_MATERIAL_CODE>

aggr(

Sum( {$<MONTH_MONTH_OF_YEAR= >} PY_PRICE)

MAT_MATERIAL_CODE)

)

Thank you a lot for your suggestions.

Cristina

Re: Displaying avg prices

Carlos_Reyes — Tue, 12 Nov 2013 16:41:08 GMT

I'm glad you found a solution.