2 Replies Latest reply on Nov 3, 2016 6:47 PM by Amanda Evans

    If/Then on Blended Datasources

    Amanda Evans

      I'm sure there's an easy way around this but I'm not able to figure this one out. Basically, I'm trying to create a simple data table which shows KPIs by date but if the date is before 9/13/13 it needs to pull a metric from the secondary datasource and if the date is after 9/13/13 it needs to pull a metric from the primary datasource. I've tried every combination of if/thens but can't seem to figure it out. Help?


      I'm unable to share the actual data so I've mocked up the issue using the superstore datasets. See attached - in this example if its before 9/13 my "Conversion Data" column should pull sales from the training datasource but if its after 9/13 it needs to pull from the superstore datasore. I have gotten the week over week table to work but if I change to month by month or day by day it breaks.


      Thanks in advance,


        • 1. Re: If/Then on Blended Datasources
          Simon Runc

          hi Amanda,


          Just looking through some unanswered questions and came across this one.


          So one way that might work is as follows....


          I've set up a parameter to control the before and after date (just helped me test it!)


          So in the Primary Data Source I set up this calculation...


          [Sales Before Order Date]

          IIF([Order Date]<[Get Data From Primary Before...],[Sales],0)


          and then in the secondary this one..


          [Discount After Order Date]

          IIF([Order Date]>=[Get Data From Primary Before...],[Discount],0)


          btw I've deliberately used discount so it's apparent when the data switches (being on such a different scale)


          Then back in the primary I create this calculation, which basically add these two together

          [Sales to Show (Discount After Date)]

          zn(SUM([Sales Before Order Date]))


          zn(SUM([CL Training Data Source].[Discount After Order Date]))


          ensuring that the blend field of Order Date is checked.


          Hope that makes sense, and does the trick, but let me know if not.

          1 of 1 people found this helpful
          • 2. Re: If/Then on Blended Datasources
            Amanda Evans

            Genius - thank you so much. Never would have figured that one out but it worked like a charm!