Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

% of total

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

32 Replies
sunny_talwar

Can you share a sample where you are facing this issue?

kunkumnaveen
Specialist
Specialist
Author

Hello sebastien,

I think what ur said is right ,there are total 800 materials in our database,so in out first sheet of my dash broad i need to show count of all those 800 materials and on second sheet i need to pick only few (about 26) based up on client list among those 800 materials....so i filtered in this way in tthe expression

EXPRESSION NAME:%OF TOTAL

=sum({$<MATNR={ 000000110812032000,  000000110813001000, 000000110813001200,000000110812010010,000000110812010300,000000110812010600,

000000110819064000, 000000287404003000, 000000287404001000, 000000110819010000, 000000110803111000,000000110803010000},ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)/SUM(TOTAL <[Raw Materials Name]>PRUEFLOS)


Note: PRUEFLOS (NO. OF LOTS)


one more thing the Raw Materials Name was created by me i mean by using inline code like this:



[Rawtable Name]:

LOAD * INLINE [

    Raw Materials Name, MATNR

    Hydrated Lime, 000000110812032000

    Hydrated Lime, 000000110813001000

    Hydrated Lime, 000000110813001200

    Hydrated Lime, 000000110812010010

    Hydrated Lime, 000000110812010300

    Hydrated Lime, 000000110812010600

    Sodium Sulphate, 000000110819064000

    Cement, 000000287404003000

    Cement, 000000287404001000

    Soda Ash, 000000110819010000

    Caustic Soda, 000000110803111000

    Caustic Soda, 000000110803010000

];

so in pivot table i took two dimension 1.rawmaterials...

                                                             2.suppilers   (both these fields i fetched from two different tables which were associated by a common field .....MATNR


I think  there should be a slight change in the expression ,finally it should show me only those materials suppiler % of total ..what i mention in the inline table ...so any help from you please


i am trying to show you those fianl table output ....but in reply box there is no option of attaching the file so how can i achieve this


thanks

naveen




       



kunkumnaveen
Specialist
Specialist
Author

Hello sunny,

I think what ur said is right ,there are total 800 materials in our database,so in out first sheet of my dash broad i need to show count of all those 800 materials and on second sheet i need to pick only few (about 26) based up on client list among those 800 materials....so i filtered in this way in tthe expression

EXPRESSION NAME:%OF TOTAL

=sum({$<MATNR={ 000000110812032000,  000000110813001000, 000000110813001200,000000110812010010,000000110812010300,000000110812010600,

000000110819064000, 000000287404003000, 000000287404001000, 000000110819010000, 000000110803111000,000000110803010000},ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)/SUM(TOTAL <[Raw Materials Name]>PRUEFLOS)


Note: PRUEFLOS (NO. OF LOTS)


one more thing the Raw Materials Name was created by me i mean by using inline code like this:



[Rawtable Name]:

LOAD * INLINE [

    Raw Materials Name, MATNR

    Hydrated Lime, 000000110812032000

    Hydrated Lime, 000000110813001000

    Hydrated Lime, 000000110813001200

    Hydrated Lime, 000000110812010010

    Hydrated Lime, 000000110812010300

    Hydrated Lime, 000000110812010600

    Sodium Sulphate, 000000110819064000

    Cement, 000000287404003000

    Cement, 000000287404001000

    Soda Ash, 000000110819010000

    Caustic Soda, 000000110803111000

    Caustic Soda, 000000110803010000

];

so in pivot table i took two dimension 1.rawmaterials...

                                                             2.suppilers   (both these fields i fetched from two different tables which were associated by a common field .....MATNR


I think  there should be a slight change in the expression ,finally it should show me only those materials suppiler % of total ..what i mention in the inline table ...so any help from you please


i am trying to show you those fianl table output ....but in reply box there is no option of attaching the file so how can i achieve this


thanks

naveen

kunkumnaveen
Specialist
Specialist
Author

Hello jagan,

can you please help me in solving this ,there are total 800 materials in our database,so in out first sheet of my dash broad i need to show count of all those 800 materials and on second sheet i need to pick only few (about 26) based up on client list among those 800 materials....so i filtered in this way in tthe expression

EXPRESSION NAME:%OF TOTAL

=sum({$<MATNR={ 000000110812032000,  000000110813001000, 000000110813001200,000000110812010010,000000110812010300,000000110812010600,

000000110819064000, 000000287404003000, 000000287404001000, 000000110819010000, 000000110803111000,000000110803010000},ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)/SUM(TOTAL <[Raw Materials Name]>PRUEFLOS)


Note: PRUEFLOS (NO. OF LOTS)


one more thing the Raw Materials Name was created by me i mean by using inline code like this:



[Rawtable Name]:

LOAD * INLINE [

    Raw Materials Name, MATNR

    Hydrated Lime, 000000110812032000

    Hydrated Lime, 000000110813001000

    Hydrated Lime, 000000110813001200

    Hydrated Lime, 000000110812010010

    Hydrated Lime, 000000110812010300

    Hydrated Lime, 000000110812010600

    Sodium Sulphate, 000000110819064000

    Cement, 000000287404003000

    Cement, 000000287404001000

    Soda Ash, 000000110819010000

    Caustic Soda, 000000110803111000

    Caustic Soda, 000000110803010000

];

so in pivot table i took two dimension 1.rawmaterials...

                                                             2.suppilers   (both these fields i fetched from two different tables which were associated by a common field .....MATNR


I think  there should be a slight change in the expression ,finally it should show me only those materials suppiler % of total ..what i mention in the inline table ...so any help from you please


i am trying to show you those fianl table output ....but in reply box there is no option of attaching the file so how can i achieve this


thanks

naveen

kunkumnaveen
Specialist
Specialist
Author

Hello SunnyT ...

Any more help can u do for me regarding this % of total ?

i think expression u have given is perfect but just need small change in that expression so that i can get grand total perfect....with this expression i am getting subtotals perfect

thanks

naveen

kunkumnaveen
Specialist
Specialist
Author

Hello SunnyT ...

Any more help can u do for me regarding this % of total ?

i think expression u have given is perfect but just need small change in that expression so that i can get grand total perfect....with this expression i am getting subtotals perfect

thanks

naveen

kunkumnaveen
Specialist
Specialist
Author

Hello Sebastien

Have u seen my reply ,please can u suggest me how to achieve this task .i got stuck with this from last 4 days

thanks

naveen

sfatoux72
Partner - Specialist
Partner - Specialist

To send file or application, you need to use advance editor (top right link on simple editor). You will find a link in the bottom right to attach file.

Mark my answer as helpfull, if it help you to understand your problem

sfatoux72
Partner - Specialist
Partner - Specialist

You add two things on your expression, but only on the first sum???

  • MATNR linked to a [Raw Materials Name]
    • I replace it by [Raw Materials Name]={"*"}
    • I add it to the second sum too
  • ENSTEHDAT
    • I don't know what it is,  I removed it
    • If you want to add it, add it in both sum

Try something like that :

=sum({$<[Raw Materials Name]={"*"}>}PRUEFLOS) / SUM({$<[Raw Materials Name]={"*"}>}TOTAL <[Raw Materials Name]>PRUEFLOS)

kunkumnaveen
Specialist
Specialist
Author

Hello Sebastien,

I seriously don't known how to thank you ,u solve my last four days headache in a minute ,thanks man u r genius

thanks

naveen