Total for pivot table expressions and increase of staffel with 0.05 %

[amber2000]

Hi everyone,

I'm struggling with 2 issues:

1)     The total in my pivot table doesn't calculate the average values of all the rows

        f.e.

        I have a zone called Flowthru

        This zone is divided into 3 PBL Zones

      

This is the result I want to see for that selection, from #Assignments on I want the average of the values

71408

480:22:51

1910

37,33

146,76

157,99

93,82%

I looked at the function Total but I don't know where I can insert it into my expression (which is already complicated)

2) The expression fol Staffel_New needs to be increased for every argument in that expression with 0.05 % and again after many attempts I don't know how to implement this in the expression.

Can somebody help me please?

Kind regards,

Monique

[sunny_talwar · Accepted solution]

Don't understand the 2nd point.... see if this looks good for 1

[amber2000]

Hi Sunny,

You are great, this is exactly what I wanted for point 1.

I'll try to explane what I'm trying to achieve with point 2:

The values of the Expression "Staffel_New" should be increased with lets say 5% (it could also be increased with 7%,10%, 15% or any number I need).

So I would like to have a slider where I can increase the % by wich I want to multiply Staffel_New(only over "Zone "/ "PBL Zone" but not over Department/ Shop_Ens that are in dimension "Group2")  if at all possible.

Here I'm trying to illustrate how the results should look after calculation in the last 3 columns :

Zone	PBL Zone	Productivity	Staffel_New	% Original	Staffel_New * 1,05	% calc = (Productivity / Staffel_New)	% New
Flowthru	Pbl Traiteur	154,31	176,82	87,27%	185,66	(154,31 / 185,66)	83,11%
	Pbl vlees	134,47	129,59	103,77%	136,07	(134,47 / 136,07)	98,82%
	Pbl Zuivel	151,50	167,55	90,42%	175,93	(151,50 / 175,93)	86,12%

I know this is quite complex but I hope you can help me with this.

Kind regards,

Monique

[sunny_talwar]

Check the attached

[amber2000]

Hi Sunny,

This looks great except I want to be able to set this variable for each different Zone

f.e.

'Zuivel gekoeld blok F' *1.05  --> vNew = 5% (This comes from dimension 'Zone')

'Zuurwaren/Mosselen' * 1.10 --> vNew = 10% (This comes from dimension 'Zone')

'Pbl Zuivel' * 1.07 --> vNew = 7% (this is a sub dimension 'PBL Zone' of Zone 'Flowthru')

'Pbl Vlees $ 1.00 --> vNew = 0% (this is a sub dimension 'PBL Zone' of Zone 'Flowthru')

Is this also possible please?

Kind regards,

Monique

[sunny_talwar]

Not sure I understand... each row will be multiplied with a different number? May be use Input fields ‒ QlikView for that.

[amber2000]

hi Sunny,

Yes you understand perfectly, each row will be multiplied with a different number if needed.

I was thinking of making per Zone a variable, something like vPblZuivel or vZuivelOngekoeld and use that variable in each of the If statements.

The problem is that I doesn't work because I don't know where in the if statement I should put the statement  *(1+vPblZuivel)

Do I make any sense?

My manager want's the increase of the % to be variable per Zone, if the target (Staffel_New) value is green then it must be increased.

Kind regards,

Monique

[sunny_talwar]

I suggest using Inputfield instead of using a variable

[amber2000]

Any idea on how I can manage this please?

Never used it and what I've just read says that it must be created in the load script.

Suppose I create an inputfield for each zone, how can I use that inputfield in the expression?

[amber2000]

Tried the inputfield but it only works in a table box and I get a row to fill in for every record of data.

Also another option was the InputSum but I couldn't change the value.

Then the next option I've tried was to include 1 variable per Zone but that's not working eather.

I'm lost now and I hope that you can find another solution that can work???

All my efforts can be seen in the included report.

Kind regards,

Monique