Hi all! I've got a (hopefully) easy question for you all. How can I use underlying data (that isn't present in the view) in a rolled-up calculation? Now, LOD's seem like the most obvious answer, but I'm having issue getting them to work with my two data sources. I may just be misunderstanding something simple so please correct me if I'm wrong!
Here's a breakdown of my data:
Datasources
1. Sales by day and ISO code
2. Daily Exchange rates (with ISO code)
I've blended these two on Date and ISO code, with Sales as my primary source. I've also created a calculated field "Converted Sales (USD)" which is simply SUM([Sales])*SUM([USD Conversion Rate])
Here's what all of that looks like in a crosstab, where converted sales is calculating correctly.
My question is: Is it possible to create a view with "Converted Sales (USD)" without including the ISO detail in the view? That is, a "rolled-up" view like below, where we're getting one monthly number (which is comprised of many converted daily sales numbers) The numbers below are obviously incorrect, as it is simply summing all the conversion rates and multiplying across.
Thanks in advance! Let me know if I can clarify any of the information I've uploaded.
Derek
Oh, silly me. I didn't realize that the exact date matching would make a difference, which is obvious now that I realize that we're working with exchange rates. I guess that's what happens when you fly through and just think about the mechanics of a solution without considering the underlying data.
So we can basically apply the same approach we used before, but we need to keep the exact date as a dimension. That means that we need to address our table calc on both Date and ISO.
Of course, you can hide the Date and ISO headers for the final product.
