% of total

[kunkumnaveen]

Hello All

I need to show % of Total,i  mean if suppose my data is like this(thinking to show either in straight table or pviot table)

rawmaterialname  suppiler    no.of lots

hydrogensulphate   birla           10

hydrogensulphate   l&t             20

hydrogensulphate   hzl            30

sulphate                birla          30

sulphate                kun            50

sulphate                nav             60

now,In my straight table or pivot table i need a column with %of total

dimensions :rawmaterial,suppiler

Table:

rawmaterialname   suppiler    %of total

hydrogensulphate  birla        i need some thing  like this(10/60)

hydrogensulphate  l&t           20/60

.

.

sulphate               birla           30/140

sulphate                kun            50/140

so please help me with expression what should i need to write down in that column....will be any change whether i display it either in straight table or pivot table

thanks

naveen

[kunkumnaveen]

Hello Sebastien

Sorry for annoying you man,i felt very happy when i thought i solve this task successfully but there is minor mistake going on ,what i mean to say is

the requirement is when a date range is selected it need to display the values between those dates.i am successful in achieve that task by writing down your expression...

it going wrong when i press clear button which clears the date range ,what i mean to say is after pressing  clear button the values remains same but all the suppiler names are popping up ..

PIVOT TABLE:

dimensions are :Raw Materials,Suppiler name

expression:

1.%of total:

=sum({$<[Raw Materials Name]={'SIPX','PEX','Copper Sulphate','Rock bolts'}>}PRUEFLOS) / SUM({$<[Raw Materials Name]={'SIPX','PEX','Copper Sulphate','Rock bolts'}>}TOTAL <[Raw Materials Name]>PRUEFLOS)

2nd expression:

no.of lots:

before ur expression i was using this:

//=count({$<MATNR={  000000110819035010,000000110816030010,000000110803030000, 000000190601660000,000000190602524100, 000000190602524000, 000000190602515000, 000000990000142600,000000249994001000,

  //000000249924005100, 000000510607800200,000000510607800100, 000000190602030000,000000190602018100, 000000190602018200,  000000190602025000, 000000990000142700 },ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

after i replaced it with ur expression like this:

=count({$<[Raw Materials Name]={'SIPX','PEX','Copper Sulphate','Rock bolts'},ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

NOTE:ENSTEHDAT (its a date field i mentioned it because i need to show values between those selected dates)

i have changed ur expression ..the raw materials are differentiated in to two types and i am showing those materials in two diff tables by using container object ..as u seen in the attachment

smelting : 'Hydrated Lime','Sodium Sulphate','Cement','Soda Ash','Caustic Soda' these all comes under smelting

mining:'SIPX','PEX','Copper Sulphate','Rock bolts' these all comes under mining ..

i will be happy to see same values and with same suppiler name which i am getting while selecting the date range even though after pressing Clear ALL button.

PLEASE FIND THE ATTACHMENT .........

thanks

naveen

[sfatoux72]

I saied you to add ENSTEHDAT if you need it.

=sum({$<[Raw Materials Name]={'SIPX','PEX','Copper Sulphate','Rock bolts'}, ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS) / SUM({$<[Raw Materials Name]={'SIPX','PEX','Copper Sulphate','Rock bolts'}, ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}TOTAL <[Raw Materials Name]>PRUEFLOS)

And if it is possible that your variable is empty, you could replace in your expression to have a default value. Your expression will be always valid :

$(vFromdate)   by someting like    $(=if('$(vFromDate)' = '','01/01/1900','$(vFromDate)'))

$(vTodate)       by someting like  $(=if('$(vTodate)' = '','01/01/2100','$(vTodate)'))

[kunkumnaveen]

Hello Sebastien

i did so and i am able to achieve this task successfully ,thankful to you man