Precalculated field in the same table

[#1]

Hi Guys,

We have a problem to solve and I would like to find an elegant and fast solution... but alone is more complicated.

I need a precalculated value to append to the following table:

Tbl_Src:

Id     IdObject     DateIni     DateEnd

The Id is unique

The IdObject is an important field to aggregate the precalc value.

DateIni always will be filled, it is the starting day.

DateEnd can be empty (if it doesn't finish yet).

We need to add the precalculated only for rows with DateEnd=[empty]

SubSet of rows to add the precalculated:

Id_filetered     IdObject_filtered     DateIni_filtered     DateEnd_filtered

We need to count how many Id are related with IdObject_filtered and DateIni between AddMonths(DateIni_filtered,-3) and DateIni_filtered.

By the moment we have set a loop using a Count(Id) with the condition explained above (calculating the number of Id for each row of the subset table), but it is very slow and I am convinced that another way exists to solve the problem.

Thank you in advanced!

[anlonghi2]

Hi Raul,

please look at attached qlik doc.

To test the approach I used the data stored in the attached excel file.

let me know if I understand correctly your needs.

Best regards

Andrea

[#3]

Thank you Andrea for you fast answer.

It is not exactly the number I needed, let me clarify a little bit:

Using your script we have the same Count for each Id_Object, but we need a differend value for each Id | Id_Object because it depends on the DateIni of each Id | Id_Object.

For exemple, using your Excel file:

Id	DateIni	DateEnd	Id_Object	Target	Actual
1	13/11/2015	15/11/2015	A	70000	200
15	11/10/2015	11/10/2015	A	70000	100
28	11/09/2015	11/09/2015	A	70000	100
2	13/11/2015	15/11/2015	B	70000	100
16	11/10/2015	11/10/2015	B	70000	100
28	11/09/2015	11/09/2015	B	70000	100
3	13/11/2015		C	70000	100
17	11/10/2015	11/10/2015	C	70000	100
28	11/09/2015	11/09/2015	C	70000	100

The value for the Id_Object = C (Id=3)... Should be equal to the number of Ids with IdObject='C' and DateIni between (DateIni-3months) and DateIni of the dates in the row Id=3. This criteria for each row with DateEnd=''. So the calculated value for each row with DateEnd='' can be different.

Thank you again,

Raul

[anlonghi2]

Hi Raul,

could you share sample data and expected results ?

Regards

Andrea

[#5]

Hi Andrea,

Sure!

For example, here the empty value should be 7 (because it is the same Id_Object and DateIni is between 28/05/2015 and 3 months before):

Please find attached a qvd with all the set of data needed.

Thank you,

Raul

[anlonghi2 · Accepted solution]

Hi Raul,

please look at attached new version.

Regards

Andrea

[#7]

Thank you Andrea!

It works and it is a very clean solution.

The Actual field (using the Rand funciont) is not needed, isn't it?

Thank you again,

Raul

[anlonghi2]

Hi Raul,

thanks for your ack.

Looking better I discovered a calculation error when DateIni of the last record to take in consideration is equal to DateIni of the record with null DateEnd...

So, please, look at attached version that optimize the pivot expression and solve the above issue.

Best regards

Andrea

[anlonghi2]

yes, you don't need it.

Best regards

Andrea

[#10]

Perfect, now we are not considering cases without EndDate.

Thank you,

Raul