Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Multiple dates and synthetic keys, the road to hell?

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.

3 Replies
mike_garcia
Contributor III

Multiple dates and synthetic keys, the road to hell?

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.

Not applicable

Multiple dates and synthetic keys, the road to hell?

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 Smiley Happy

Not applicable

Multiple dates and synthetic keys, the road to hell?

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

Community Browser