topic Re: Re: Keeping fixed value regardless the drill in QlikView

Keeping fixed value regardless the drill

Mon, 11 Nov 2013 10:37:54 GMT

Hi to all,

I'm facing an issue regarding the association of data in my pivot table.

I'm displaying the average price for each material split up by months.

I use this calculation to get my average price: avg(TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE)) and it works if I show up all the months. When I click on one single month, I get an incorrect value. Normally, the average of all months is the same and I wouldn't expect to change although I click on one single month.

How can I do in order to keep the value fixed regardless the month I click on?

Thanks

Cristina

Re: Keeping fixed value regardless the drill

bruno_m_santos — Mon, 11 Nov 2013 10:43:36 GMT

I suggest to ignore the month in your setAnalysis expression.

Something like this:

=avg(TOTAL {$<Month=, MAT_MATERIAL_CODE>} aggr(PYprice, MAT_MATERIAL_CODE))

Hope it helps

Re: Keeping fixed value regardless the drill

rustyfishbones — Mon, 11 Nov 2013 10:48:17 GMT

If you are using Set Analysis

here is an example were we want the sum of the quantity

It will not change if Date, Country or ID Number is selected, you need something similar I think

Re: Keeping fixed value regardless the drill

jonathandienst — Mon, 11 Nov 2013 11:12:40 GMT

I suspect that you need to include the month in the aggregation:

=avg(TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE, month))

=avg(TOTAL <MAT_MATERIAL_CODE, month> aggr(PYprice, MAT_MATERIAL_CODE, month))

HTH

Jonathan

Edit:

I think that I misunderstood your requirement - to override the month selection:

=avg({<month=>} TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE))

Jonathan

Re: Keeping fixed value regardless the drill

Mon, 11 Nov 2013 11:17:36 GMT

Thanks for your suggestion, I've tried to apply it but unfortunately it doesn't work. The values still change

Re: Keeping fixed value regardless the drill

Mon, 11 Nov 2013 11:19:59 GMT

Including the Month in the aggregation I get wrong values. The correct formula is that specified above.

Re: Re: Keeping fixed value regardless the drill

Mon, 11 Nov 2013 11:26:59 GMT

Please, find attached my application

Re: Keeping fixed value regardless the drill

bruno_m_santos — Mon, 11 Nov 2013 11:27:31 GMT

f you use setanalysis, you can ignore your selections.

Re: Keeping fixed value regardless the drill

rustyfishbones — Mon, 11 Nov 2013 11:43:50 GMT

Your file won't open

Re: Re: Keeping fixed value regardless the drill

Mon, 11 Nov 2013 13:51:59 GMT

I don't understand why my file can't be open. My uploaded applications are never able to be open.

Am I doing something wrong?

Re: Keeping fixed value regardless the drill

Mon, 11 Nov 2013 17:02:13 GMT

Hi,

I've tried to reproduce the formula you suggested but it doesn't still work.