4 Replies Latest reply: Sep 2, 2016 3:51 AM by Arabela Rondic RSS

    Qlikview - sum in script

    Arabela Rondic

      Hi,

       

      i have script

           LOAD * INLINE [

           Document, Date, Itam, Line, Saldo, Unit

            Doc1, 12.12.2015, Item1, 1, 10, m

           Doc1, 3.04.2015, Item2, 2, 15, kg

           Doc2, 11.09.2015, Item1, 1, 6, kg

           Doc3, 11.04.2015, Item1, 1, 13, m

           Doc3, 21.03.2015, Item5, 2, 45, l

           Doc4, 20.04.2016, Item4, 1, 7, l

           Doc5, 12.01.2016, Item1, 1, 13, kg

           Doc6, 7.09.2016, Item3, 1, 16, m

           Doc6, 21.09.2016, Item3, 2, 21, kg

           Doc7, 1.01.2016, Item2, 1, 3, l ];

       

      I want to get sum of Saldo by year in script. How can I modify script to get something like this:

           Load

           Year2015,

           SumOfSaldo2015,

           Year2016,

           SumOfSaldo2016

       

      And when I call SumOfSaldo2015 in text box to get sum of saldo for year 2015

        • Re: Qlikview - sum in script
          Krishnapriya Arumugam

          Get the Year column separate in script

          Try below expression

          Sum({<Year={"2015"}>}Saldo)

          • Re: Qlikview - sum in script
            Peter Cammaert

            Or add this at the end of your script

             

            Aggregated:

            LOAD Year(Date#(Date, 'D.MM.YYYY')) AS Year,

                 Sum(Saldo) AS SumOfSaldo

            RESIDENT InlineTable

            GROUP BY Year(Date#(Date, 'D.MM.YYYY'));

             

            I'm sorry but I don't understand why you want to store Years and Saldi in different columns (QlikView doesn't really like it that way).

             

            Peter

            • Re: Qlikview - sum in script
              Anil Babu

              Let v2015 = Sum({<Date = {=Min(Year(Date))}>} Saldo);

              Let v2016 = Sum({<Date = {=Min(Year(Date))}>} Saldo);

               

              LOAD Min(Year(Date)) as Year2015,

                   '$(v2015)' as SumOfSaldo2015,

                   Max(Year(Date)) as Year2016,

                   '$(v2015)' as  SumOfSaldo2016

              INLINE [

                   Document, Date, Itam, Line, Saldo, Unit

                    Doc1, 12.12.2015, Item1, 1, 10, m

                   Doc1, 3.04.2015, Item2, 2, 15, kg

                   Doc2, 11.09.2015, Item1, 1, 6, kg

                   Doc3, 11.04.2015, Item1, 1, 13, m

                   Doc3, 21.03.2015, Item5, 2, 45, l

                   Doc4, 20.04.2016, Item4, 1, 7, l

                   Doc5, 12.01.2016, Item1, 1, 13, kg

                   Doc6, 7.09.2016, Item3, 1, 16, m

                   Doc6, 21.09.2016, Item3, 2, 21, kg

                   Doc7, 1.01.2016, Item2, 1, 3, l ];