32 Replies Latest reply: Feb 16, 2016 6:40 AM by naveen kumar RSS

    % of total

    naveen kumar

      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

        • Re: % of total
          Sunny Talwar

          Try this:

           

          Sum([no.of lots])/Sum(TOTAL <rawmaterialname> [no.of lots])

            • Re: % of total
              naveen kumar

              Hello Sunny ,thanks for very quick reply ,every thing is working fine expect the the final total well if everything is 100% obsivouly the final total has to be 100% am i right ,i there any option to remove Total only to that particular column please find the attachment

              thank naveen 

                • Re: % of total
                  Sunny Talwar

                  I don't see any attachments

                  • Re: % of total
                    Sébastien Fatoux

                    On expression tab, select your expression and modify this option to remove Total for this column

                    2016-02-08 22_56_00-Qlik Community_204813.png

                    • Re: % of total
                      Byron Van Wyk

                      Can't you just use the relative % tick box in a straight table to give you what you want

                       

                      Capture.PNG

                        • Re: % of total
                          Jonathan Dienst

                          That option will be % of the total data set. I think the OP wants it by raw material. Sunny has that right.

                          • Re: % of total
                            naveen kumar

                            Hello Byron

                            unfortunately see that TOTAL MODE option its graded out,i mean its not letting me to select any option of that particular expression ,please have u got any idea how to get away from this grand Total only for this particular column

                            thank you

                            naveen

                              • Re: % of total
                                jagan mohan rao appala

                                HI Naveen,

                                 

                                That Total Mode will be enabled for Straight Table, I think you are using Pivot table, that is why it is disabled.

                                 

                                Can you attach your expected output so that it is easier to understand.

                                 

                                Regards,

                                Jagan.

                                  • Re: % of total
                                    naveen kumar

                                    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

                          • Re: % of total
                            AC BC

                            Hi Sunny ,

                             

                            Will the expression you gave will give the expected by naveen ??

                             

                            I tried but getting differently.

                             

                            Thanks

                            Kiran Kumar

                              • Re: % of total
                                Sunny Talwar

                                Isn't this is what is needed?

                                 

                                Capture.PNG

                                  • Re: % of total
                                    AC BC

                                    Hi Sunny,

                                     

                                    Apologies for your expression I was mistaken but mean while can you test mine also, I was able to achieve in this way also.

                                     

                                    thanks

                                    Kiran Kumar

                                    • Re: % of total
                                      naveen kumar

                                      Hello sunny ,

                                      Thanks for your quick response but sunny wats the grand total we might expect when both the subtotal are showing 100% as far as i think the grand total has to be 100% am i right ,in my case i am getting all subtotal perfect i mean 100% but the grans total is showing about 94% and i am literally not able to expalin to my client when he ask me about that grand total,i tried to get rid of that grand total to the particular column by going in to  expression tab------>no total but unfortunately the NO TOTAL check box is graded out i mean its not letting me to check any option ...so sunny is there any solution for this please

                                      thanks

                                      naveen

                                        • Re: % of total
                                          Sébastien Fatoux

                                          Perhaps have you some "no.of lots" without rawmaterialname  associated

                                            • Re: % of total
                                              naveen kumar

                                              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


                                               



                                               

                                                     



                                              • Re: % of total
                                                naveen kumar

                                                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

                                                  • Re: % of total
                                                    Sébastien Fatoux

                                                    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)

                                                      • Re: % of total
                                                        naveen kumar

                                                        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

                                                        • Re: % of total
                                                          naveen kumar

                                                          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

                                                            • Re: % of total
                                                              Sébastien Fatoux

                                                              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)'))

                                                      • Re: % of total
                                                        Sunny Talwar

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

                                                          • Re: % of total
                                                            naveen kumar

                                                            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

                                                              • Re: % of total
                                                                naveen kumar

                                                                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

                                                              • Re: % of total
                                                                naveen kumar

                                                                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

                                                        • Re: % of total
                                                          AC BC

                                                          All,

                                                           

                                                          I earlier tried like this to get what naveen wants

                                                           

                                                           

                                                          Sum([no.of lots])

                                                          /

                                                          Max(aggr( NODISTINCT Sum([no.of lots]),rawmaterialname))

                                                           

                                                           

                                                          thanks

                                                          Kiran Kumar