I need to create a dashboard where I show our sales and the comission that we will get from our supplier. Those comissions are set in ranges and each supplier has different ones.
I've attached a cvs file with the borders of the comission by supplier, our sales by date and the expected results.
I have tried to get the results using Intervalmatch function as follows:
SALES: LOAD "Date", Supplier, Total_amount FROM [lib://AttachedFiles/example_interval.xlsx] (ooxml, embedded labels, table is Sales);
INTERVALS: LOAD Supplier, A, "B", Comission FROM [lib://AttachedFiles/example_interval.xlsx] (ooxml, embedded labels, table is Comission);
IntervalMatch(Total_amount) LOAD A, "B" Resident INTERVALS;
I've also used an innerjoin to avoid de loop in the data, but I haven't got any results.
I also have the following question: In case I make intervalmatch works in script, Could I use set analysis and filter by dates afterwards in my dashboard? I am afraid that, since it is done in the script, it won't be dynamic and I won't be able to filter it by date.
Thanks a lot for your time, in case you need me to explain myself more clearly let me know.
thanks a lot for your quick anwer. I have attached two images that shows whats im seeking for. I would need a table where I could see the total amount by supplier for the dates selected and the corresponding comission % depending on the range of sales by supplier. Btw, the link that you sent appears as :
The page does not exist. It may have been renamed or moved.
Interresting problem. You can not use intervalmatch if you expect the range to be dependent on your selections. I thougth you wanted the commission to be appplied on a transaction level.
To solve this you do probably need some kind of smart SET-analysis, I've tried but got stuck. Sorry!
