Value between dates

[#1]

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

[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.

[rubenmarin]

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.

[Anonymous]

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?

[#5]

You can make an example with the file I sent?

Thank you.

[#6]

You can make an example with the file I sent? Thank you.