4 Replies Latest reply: Jul 20, 2011 2:13 PM by Stefan Wühl RSS

    sum amount based on entry code

      Hello,

       

      I am new with Qlikview and very much interested in learning it. In view of this and in hope someone to give me a headstart to develop my 1st Qlikvie application.

       

      My source file contains records for the below fields

      Business IDWorksheet IDDate of BookingEntry CodeAmount

       

      I would like to develop a report to look like this by utilizing the (Entry Code and Date of Booking)

      EntryCode GroupYTD TYYTD LYLast Year
      Premium
      Acquisition
      Net Premium

       

       

      The Entry Codes that make-up the EntryCode Group is as below i.e.

       

      Premium = where Entry Code in [4001, 4003, 4005] then Amount

      Acquisition = where EntryCode in [6003, 6704, 6707, 6708, 6801] then Amount

      Net Premium = Premium + Acquisition

       

      Obliged if you can help me.

      George

        • sum amount based on entry code
          Stefan Wühl

          Hi George,

           

          I am pretty sure someone here can help you.

           

          Personally, I don't understand the then statements of you group classification. Could you explain that a bit more?

           

          Talking about Net Premium: Should this be the superset / union of the two Groups above?

          If I hear about Net, I always think about a difference

           

          Regards,

          Stefan

            • sum amount based on entry code

              Hi Stefan,

               

              Thanks for getting back to me.

               

              The sum of Premium (say 100) plus the sum of Acquition (say -20) will make Net Premium amount.

               

              I hope with this example I have clarified the concern.

               

              Thanks,

              George

                • Re: sum amount based on entry code
                  Stefan Wühl

                  Hi George,

                   

                  that looks like a point in time expression using set analysis:

                   

                  You may try something like

                  Last Year :

                  sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<$(=yearstart(today())>=$(=yearstart(today(), -1))"}>} Amount)

                   

                  YTDTY:

                  sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<=$(=today())>=$(=yearstart(today()))"}>} Amount)

                   

                  YTDLY:

                  sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<=$(=addmonths(today(),-12))>=$(=yearstart(today(),-1))"}>} Amount)

                   

                  (For Premium, replace Entry codes for Acquisition, use sum of expressions for net)

                   

                  I probably forgot a bracket or two and also maybe mistyped some expressions (hopefully not), so please take care of that (The syntax checker in expression dialog has difficulties if you use quotation marks, AFAIK).

                   

                  If you want to learn more about set analysis, please have a look at the manual or

                  http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

                   

                  Regards,

                  Stefan

                    • Re: sum amount based on entry code
                      Stefan Wühl

                      One more thing:

                       

                      the first "1" at the beginning of the set expressions has the effect, that your currect selection will not be taken into account.

                      I think you might want to select Business ID and Worksheet ID, so it is better to delete the "1" at the beginning (equivalent to use a "$" instead).

                       

                      Have fun,

                      Stefan