5 Replies Latest reply: Nov 13, 2015 8:20 AM by Gleybson Fernandes RSS

    Value between dates

      Hello people,

      I have a big problem to be solved with set analysis.

      I want to add a value in a given time period.

      Table:

      cod. value

      1      150

      2      250

      3      120

      4      320

      5      550

      for me to get the values to have to put up on the set analysis an initial period and end date.

      Example:

      cod        start date             end date

      1             20150325       20151112

      2             20150410       20151112

      3             20150430       20151112

      4             20150502       20151112

       

      attachment follows a part of the project.

      The value of cod 103460 is 8.511,25 and when selected will for the value of 6.345,22 .

      I need this amount of 6.345,22 stay without having to select the cod

        • Re: Value between dates
          Gysbert Wassenaar

          It sounds like you want to use the Intervalmatch function. But I'm not going to download 25MB to find out and the example you posted isn't clear about what kind of data you're working with. So, have a look at this blog post: IntervalMatch. If that's not what you need please post a small qlikview document that illustrates the problem.

          • Re: Value between dates
            Ruben Marin

            Hi Gleybson, set analisys and $-expansion is calculated before the table, so it will not affect row by row, you can check the difference removing the header of the expression and showing how it changes when you filter by a cod.

             

            You can use Aggr() or If to apply conditions row by row, something like:

            Sum(If(ID_CALENDARIO>=ID_ANIVERSARIO_CONTRATO_BENEFICIARIO_INI

                    and ID_CALENDARIO<=ID_ANIVERSARIO_CONTRATO_BENEFICIARIO_FIM, FATURAMENTO.VALOR))

            // (It's just an example)

             

            But in your model gets complicated because the expression involves different tables and the comparisons gives strange results... I'm not sure why but i would try to have all the 'filter' fields in the CARTEIRA table and check if works better.

            • Re: Value between dates
              balraj ahlawat

              if you are having this data into two tables, then use applymap(), like:

               

              Table1:

              mapping

              cod. value

              1      150

              2      250

              3      120

              4      320

              5      550

               

              Table2:

              cod        start date             end date         applymap('Table1','cod',null() as Value

              1             20150325       20151112                    150

              2             20150410       20151112                     250

              3             20150430       20151112                     120

              4             20150502       20151112                     320 

               

              is this?