Multiple dates and synthetic keys, the road to hell?

[#1]

Hi!

I am apart of a group creating a DSS as a university assignment, we are having a problem. The system is currently linking Orders on Employee ID. From order we get the date variables year, month and week. When we want to calculate the yearly costs for a selected year we need to be able to get this information from hire date as well (in order to determine who was hired at the specific date (at the moment we only get the sales people since they are in Orders)). Creating more year variables creates synthetic keys and we are not allowed that. Is there a way to do this by creating a table or perhaps in the code for the graphs? We tried creating some tables but we got circular references. Any help is much appreciated.

[mike_garcia]

Hi Johan,

Assuming you want to have only one list box for year, one for month, and one for week, and that the selections in those list boxes apply for dates in the Orders Table, as well as in the Hire Table, etc, you can use set analysis to solve the issue right in the object.

Sum({$<HireYear = p(OrderYear)>} Field)

That will give you the sum for the data associated with the Hire Year that equals the selections in the Order Year list box, just as if you were selecting the HireYear itself.

Note: Functions p() and e() only work in version 9.

I hope I understood your needs correctly and this explanation is clear enough and helps you.

Mike.

[#3]

hmmm, we tried to use this code but qlikview still doesn't find the employees for yearly salarey who have not sold.

Thanks for helping though 🙂

[#4]

Have you looked at joining tables to get around the circular references?