3 Replies Latest reply: Oct 27, 2016 8:41 AM by Matheus Colares RSS

    Values Between Date!

    Matheus Colares

      Hi guys.

      Now, i have a situation witch i belive not heva a solution, but i will ask...

      There is way to make in script , sum a sales values between two dates...for example:

       

      LOAD * INLINE

      [STORE,DATE

      001, 11/05/2016

      002, 02/06/2016

      003, 20/04/2016

      002, 04/08/2016,

      001, 11/09/2016,

      003, 14/10/2016];

       

      I have in other .qvd the sales by store and day, good, now i want make a logic in qlikview that will do this:

       

      001, 11/09/2016 , $ 60000  --where this "$6000" is the sum between 11/05/2016 and 11/09/2016

      002, 02/06/2016 , $ 3000    --where this "$ 3000" is the sum between 02/06/2016 and begin

      003, 20/04/2016   $ 45790  --where this "$ 45790" is the sum between 20/04/2016 and begin

      002, 04/08/2016,  $ 5000   --where this "$ 5000 " is the sum between 02/06/2016 and 04/08/2016

      001, 11/05/2016,  $ 23456 --where this "$ 23456" is the sum between 02/06/2016 and begin

      003, 14/10/2016  $ 78998  --where this "$ 78998" is the sum between 20/04/2016 and 14/10/2016

       

      Understood?, I mean, i will sum sale between DATE and previous DATE found for that Store...

      I can't think in nothig to resolve this solititation....

       

      Thank you!!!

        • Re: Values Between Date!
          Alluraiah Allu

          May be try with interval match ..

          • Re: Values Between Date!
            Andrew Walker

            Hi Matheus,

                                Try this script:

            [Store Dates]:

            LOAD * INLINE

            [STORE,DATE

            001, 11/05/2016

            002, 02/06/2016

            003, 20/04/2016

            002, 04/08/2016,

            001, 11/09/2016,

            003, 14/10/2016];

             

            [Store Dates1]:

            LOAD

            STORE,

            Date#(DATE) as ToDate,

            If(STORE=Previous(STORE),Date(Date#(Previous(DATE)) +1),Date#( '01/04/2016') ) as FromDate

            Resident [Store Dates] Order by STORE, DATE;

             

             

            DROP Table [Store Dates];

             

            This creates this table with proper intervals on which you can perform an IntervalMatch with extended syntax. I guessed at a begin date of 01/04/2016.

            STORE FromDate ToDate
            00101/04/201611/05/2016
            00112/05/201611/09/2016
            00201/04/201602/06/2016
            00203/06/201604/08/2016
            00301/04/201620/04/2016
            00321/04/201614/10/2016

             

            If your sales table has fields STORE and SALES then try an IntervalMatch along these lines:

             

            IntervalMatch:

            IntervalMatch (SALES,STORE) LOAD FromDate, ToDate, STORE resident [Store Dates1];

             

            This will give you a synthetic key but don't be alarmed. This is to be expected and is not a problem. When QlikView creates a synthetic key as a result of a properly implemented IntervalMatch the synthetic key it creates is an efficient link table allowing intervals in one table to be associated with distinct values in another.

             

            Good luck

             

            Andrew

              • Re: Values Between Date!
                Matheus Colares

                Andrew, It worked...

                I did like you said and it worked....But i dont got it how use intervalmatch, but i did by other way, because i needed sum sales values between date begin_and date_end, but with intervalmatch i dont get it...But anyway, it worked a lot...Thank you and thanks for all who help....