9 Replies Latest reply: Feb 16, 2017 12:50 AM by Agrim Sharma RSS

    script help please

    Agrim Sharma

      Hi,

      i wanted to calculate month wise(M1...M12) Enable, Other, Run, Ref. Build, so that i do not have to use M1...M12 in making Dashboard,

      with the help of formula i wanted to calculate month wise Enable, Other, Run, Ref. Build without including M1...M12 tables in qlik sense.

      attached is my current script. In this script i have included M1....M12 but i do not want to Include these tables in dashboard.

        • Re: script help please
          Kaushik Solanki

          Hi Agrim,

           

          First of all please do not create multiple post of same question.

           

          Now I am going through your excel, can you tell me what is the logic written because I am not excel expert. It would be helpful if you take one emp as example and explain how the values are arrived.

           

          Regards,

          Kaushik Solanki

            • Re: script help please
              Agrim Sharma

              i realize it, m sorry,

              my logic is like this for

              i want to calculate Enable, Other, Run, Ref. Build,

               

              SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],

               

               

                  SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,

               

               

                  SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,

               

               

                  SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable

               

              but  i do not want to include M1...M12 in my app.

                • Re: script help please
                  Kaushik Solanki

                  Hi,

                   

                  Its not possible to understand what you have in applymap.

                   

                  Can you explain as per your excel. Like how did you got that value for M1. I assume that M1 logic is same in other sheets like M2,M3.... and so on, excel a month change.

                   

                  Regards,

                  Kaushik Solanki

                    • Re: script help please
                      Agrim Sharma

                      yes exactly its same logic for all M1..M12

                      from my excel logic is-i want to calculate (employee's actual working days wise, role wise, team wise =reference BUILD,OTHER,RUN,ENABLE)SHEET 1

                       

                      for example

                      SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD]

                      • Re: script help please
                        Agrim Sharma

                        hi Kaushik did you got something after seeing data?

                          • Re: script help please
                            Kaushik Solanki

                            Agrim,

                             

                            I am still not getting the logic..

                             

                            I dont want to see what logic you applied in Qlik.

                             

                            I want a generic logic. Meaning what is being multiplied by what and when?

                            Hope its clear.

                             

                            Regards,

                            Kaushik Solanki

                              • Re: script help please
                                Agrim Sharma


                                thanks for replying.


                                Eg. for an employee(xyz) if I want to calculate Ref. Build then,


                                REF BUILD%(From sheet-ROLES) of Actual Working Days(From Sheet-Actual Working Days)=X

                                 

                                 

                                Role %(From Sheet-Emp vs Role) of X=Y


                                Team%(From Sheet-Emp vs Team) of Y=Z


                                Z=Ref. Build


                                like this i want to calculate for the OTHER, RUN & Enable in month wise manner.

                                therefore i used below formula.

                                SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD]

                                 

                                 

                                but i do not want to include M1..M12 sheets in Qlik Sense. i want to calculate this from ROLES,Emp vs Role,Emp vs Team & Actual Working Days Sheets only.

                                • Re: script help please
                                  Agrim Sharma

                                  Hi Kaushik,

                                  hope you fine:)

                                  i have sent you description of my logic, hope you understood my point of view for the calculations. i will wait for your kind help.

                                  Thanks & Regards,

                                  Agrim Sharma

                          • Re: script help please
                            Agrim Sharma

                            123.PNG

                            to get this chart.