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]

Check the variable... does it seem to be working?

[amber2000]

Hi Sunny,

Thank you so mutch, it works great for the dimension [FlowType Description], this is almost what I need.

I also want to include the Dimension [Zone] in the Pick(Wildmatch()) function for the other variables.

Can you show me how in the report please?

Kind regards,

Monique

[sunny_talwar]

Would you be able to provide an example of what you mean? just so that I can follow.... heading out for a little bit, but will respond as soon as I am back

[amber2000]

Sunny,

The last statement you have added should also be able to use the other variables for Dimension [Aisle Zone Description]

So a combination of the 2 dimensions.

Something like this (pseudo code)

* Pick(WildMatch([FlowType Description], 'Pbl Zuivel', 'Pbl Vlees', 'Pbl Traiteur'),

(1+vPblZuivel), (1+vPblVlees), (1+vPblTraiteur)),

[FlowType Description], [$(=GetCurrentField(Group2))]))

and

* Pick(WildMatch([Aisle Zone Description], 'Zuivel gekoeld blok F', 'Zuivel Traiteur gekoeld blok H', 'Zuurwaren/Mosselen', 'Zuivel ongekoeld', 'GRF 4de gamma', 'GRF gekoeld'),

(1+vZVLgek), (1+vTraitGek), (1+vZwMossel),  (1+vZVLong), (1+v4deGamma), (1+vGRFgek)),

[Aisle Zone Description], [$(=GetCurrentField(Group2))]))

This example hopefully gives you a better clue of how it should look like

I'll wait patiently for your return

Kind regards,

Monique

[sunny_talwar]

See if now looks like what you want

[amber2000]

Sunny,

You performed magic, it works and it is so easy.

There is only one obstacle that's not calculated correct yet and that's the column %StaffelNew

The last Column is the correct result of (Productivity / % StaffelNew)

Productivity	Staffel_New	%	%Variables	% StaffelNew	Productivity / %Variables
154,31	176,82	5%	185,66	87,27%	83,11%
134,47	129,59	15%	149,03	103,76%	90,23%
151,5	167,55	2%	170,90	90,42%	88,65%
146,76	157,99		168,53	93,82	87,33%
191,3	204,4	20%	245,28	93,59%	77,99%
184,28	190,77	0%	190,77	96,60%	96,60%
18,54	52,58	10%	57,838	35,26%	32,06%
142,83	135,89	0%	135,89	105,11%	105,11%
129,18	108,85	0%	108,85	118,68%	118,68%
139,33	145,63	0%	145,63	95,67%	95,67%
137,68	112,59	0%	112,59	122,29%	122,28%

The expressions are so complex now that I can't interfere no more, can you assist with the last hurdle please?

I apologize for the amount of time I'm absorbing from you.

Kind regards,

Monique

[sunny_talwar]

Are you looking to fix this?

[amber2000]

Sunny that's the fix I wanted to do. You are my HERO

I've enabled the column and after a complete check I see that it's correct. So sorry for wasting your time.

As everything is working now maybe I can ask you to explane  some of the changes you made and why you've made them.

Especially the statement for the calculation of the avg totals because it's completely different from the original statement:

if([Aisle Zone Description] = 'Zuivel gekoeld blok F'
,#Colli/#Assignments * pick(match(floor(#Colli/#Assignments),
     $(=concat(ProdStafValue_F, ',', RowNo))), 
    $(=concat(ProdFactorValue_F, ',', RowNo)))
,

//Else
#Colli/#Assignments * pick(match(floor(#Colli/#Assignments), 

$(=concat(ProdStafValue_GRF, ',', RowNo))),

$(=concat(ProdFactorValue_GRF, ',', RowNo)))

)

Changed to:

Avg(Aggr(if([Aisle Zone Description] = 'Zuivel gekoeld blok F'

  ,sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli])/count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT([Assignment ID]))   * pick(match(floor(sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli])/count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT([Assignment ID]))  ),

  $(=concat(ProdStafValue_F, ',', RowNo))), 

  $(=concat(ProdFactorValue_F, ',', RowNo)))

  ,

//Else

sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli])

  /count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT([Assignment ID]))  

  * pick(match(floor(sum({$<[Jobcode Flag] = {"Productive"}>}[Assignment Total Colli])

  /count({$<[Jobcode Flag]= {"Productive"}>} DISTINCT([Assignment ID]))  ), 

  $(=concat(ProdStafValue_GRF, ',', RowNo))),

  $(=concat(ProdFactorValue_GRF, ',', RowNo)))

), [FlowType Description], [$(=GetCurrentField(Group2))]))

Kind regards,

Monique

[sunny_talwar]

Check the attached

[amber2000]

I'm confused, where should I look in the report?