2 Replies Latest reply: Oct 3, 2012 5:31 AM by Kiran Shinde RSS

    Set Analysis for Summing two or more values for latest transaction date

    Kiran Shinde

      Hi Everyone,

       

      I want to write set expression for following condition :

      For one item,check latest transaction date.

      Get the Unit Cost for that Latest Transaction Date,

      If multiple unit cost present then sum (Unit Cost) over that same transaction & show this value against that Item in straight table.

      How I can do this?

       

      Thanks.

        • Re: Set Analysis for Summing two or more values for latest transaction date
          Deepak Kurup

          HI Kiran,

           

          Its a tricky one. You can use the below one to get the desired result.

           

           

          SUM({<Date = {$(=CHR(39)&CONCAT(aggr(mAX({<Date = {">=$(vFromDate) <=$(vToDate) "}>}Date),Item),CHR(39)&','&CHR(39))&CHR(39))}>} UnitCost)

           

          The formula is as follows.

           

          Chr(39) is for  ' (single quote)

           

          vFromDate and vToDate are variables from two calendar provided to users

           

          we are trying to get Maximum date for each itemid (in your case it might be other dimension) fro the selected two dates.

           

           

          Since each item can have there own maximum dates and hence we need to use concat function to get multiple dates

           

          Our expression will be read by qlikview as

           

          SUM({<Date = {'01/01/2012','02/02/2012'} UnitCost)

           

          where 01/01/2012 will be maximum date for item A

          and

          02/02/2012 will be maximum date for item B

           

           

          I hope its clear. If not then let me know

           

           

          Deepak