Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Best way to to calculate royalty depending on date of sale

Hi!

I have 3 tables:

SALES (with one row per sale of a product). Fields:

productid

date

price

PRODUCTS (with one row per product that can be sold). Fields:

productid

royaltyid

ROYALTIES (with one row per royalty amount per product and a date range)

royaltyid

amount

startdate

stopdate

How these tables are linked is pretty simple, with one product per sale. And one royalty per product and date of sale.

How would you calculate the sum of sales in this case? I would like to do something like "Sum(price - amount)" in my app, but that only works if I only have one fixed royalty for each product. In my case, some products have varying royalty amounts depending on date of sale, which makes things more complicated. I guess I could join the royalties table onto SALES during load-time using conditionals, or I could do some fancy Set Analysis in the App. How would you do it and why?

Cheers

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I would use the intervalmatch() function on the startdate and stopdate of your ROYALTIES table.

This blog post IntervalMatch by Henric explains the method well..

View solution in original post

2 Replies
Anonymous
Not applicable
Author

I would use the intervalmatch() function on the startdate and stopdate of your ROYALTIES table.

This blog post IntervalMatch by Henric explains the method well..

Not applicable
Author

Thanks, thats it!

I am a little bit afraid that I will count sales twice if I accidentally create overlapping periods in my ROYALTIES-table.