10 Replies Latest reply on Oct 5, 2016 9:19 AM by Derek Wong

    How can I use underlying data (that isn't present in the view) in a rolled-up calculated field?

    Derek Wong

      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