topic Re: Best way to to calculate royalty depending on date of sale in QlikView

Best way to to calculate royalty depending on date of sale

Sun, 01 Mar 2015 19:38:30 GMT

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

Re: Best way to to calculate royalty depending on date of sale

Anonymous — Sun, 01 Mar 2015 20:02:12 GMT

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

Re: Best way to to calculate royalty depending on date of sale

Mon, 02 Mar 2015 08:34:30 GMT

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.