1 Reply Latest reply on Jan 16, 2012 6:50 AM by Peter Hopwood

    Case Statement Troubles

      My data contains data for new visitors and return visits. I have a formula that will give me Return Visits by Total Visits - New Visits = Return Visits.  However, in order to get the view I would like, I have a "Visit Lookup" datasource that will allow me to get the desired view, but the case statements that I am trying are not working and I don't know why.

      In sheet 1 the Return Visit calculated field works and on sheet 2 the return visitor calculated field works, but when trying to reference to them on sheet 3 with the case statement they won't populate.

      I have used this method before in order to get some conversion numbers to aggregate correctly. If somebody has some insights on how to change my calculations to get it to work, I'm all ears.

      Desired View is also given.

        • 1. Re: Case Statement Troubles
          Peter Hopwood



          I can see what the problem is; it is down to a join problem between your data sources. Your sheet 3 is joining the ChannelData and Visit Lookup data sources on the Visit Type field (Data/Relationships) - this is doing effectively an inner join. If you look in your channeldata source you will see that there is only Visit Type data for 'New' and 'Total' - there isn't a value for 'Returns' so when it joins to the data with 'New', 'Returns' and 'Total' (in the Visit Lookup source) the returns data doesn't join. This means that your Case statement [Visit Revenue] never equals 'Return'


          You can see this happening if you add the [Visit Type] field to Sheet 1 or  the [channeldata].[Visit Type] to sheet 3.


          One solution I can think of is using Data/Tables to join the sources (then you can specify an outer join) instead of Data/Relationships, or in this instance break the relationship between the ChannelData and VisitLookup sources  and you get the result. The danger in this approach is that you need to ensure no other [Visit Type] data can makes its way into this source and get your numbers wrong.


          I've attached my version.


          Hope this helps