1 2 Previous Next 16 Replies Latest reply on Aug 3, 2016 7:49 AM by James Linnett

    Filtering based off calc field

    James Linnett

      Hi,

       

      I'm having filtering issues!

       

      I'm using online web traffic data and I want to get an understanding of where a customer goes after they have been on a "checkout review" page. I have built in a "Interactions field" within the dataset eg. Interaction 1 = Homepage, 2= Product Page 3= checkout etc (multiple variances based on a customers individual journey).

       

      So far I have created the following calculated fields:

       

      1. COR Interaction (This gives me the interaction number of a checkout review page)

       

       

      2. This should then tell me based off a typical interaction where the checkout review page falls in comparison to other pages.

       

       

      The problem i get is that Tableau isn't picking out the differences in point 2. where the interaction matches it works, aside from that it seems to think all other interactions are after the checkout review page (example below)

       

      Any help would be greatly appreciated.

       

      James

        • 1. Re: Filtering based off calc field
          Mark Fraser

          Hi James

           

          Easy stuff first... I see this often

          Are there multiple interactions?

           

          Does your interaction field (or COR interaction) have SUM([interaction] or is it just [interaction]?

          My guess is wrapping SUM() round the calculations will help.

          So... IF SUM([interaction]) = ... SUM([Interaction])

          ELSEIF SUM([interaction]) < .... SUM([core Interaction])

           

          Or you need to add something to split each set of records out from each other...

           

          If nothing above helps, if you could provide a small fake sample, that would be appreciated.

           

          Cheers

          Mark

          • 2. Re: Filtering based off calc field
            James Linnett

            Hi Mark,

             

            Thanks for replying so quickly.

             

            Unfortunately that didn't appear to fix the issue at hand. I have attached a fake workbook as requested.

             

            James

            • 3. Re: Filtering based off calc field
              Luciano Vasconcelos

              Can you build a sheet with your needs to show us how should be?

              • 4. Re: Filtering based off calc field
                James Linnett

                Hi Luciano,

                 

                What I'm expecting to see is any interactions that were made before the 'Checkout Review' page are clearly labelled as such. I eventually want to filter these out so I can see where site traffic go to once they have clicked 'Checkout Review'. At the moment I cant do this as it's labeling all interactions, prior and post as 'After Checkout', which isn't the case.

                • 5. Re: Filtering based off calc field
                  Mark Fraser

                  Hi James

                   

                  Thanks, I get the problem now.

                   

                  The method you're trying won't work in its current state.

                  The problem relates to returning 0 in the false... that won't work.

                  To demo, I will fake an answer, and I hope that will help explain

                   

                  What i really think you need -

                  Would you have a date/time in the data set? Someway of sorting the data on when the activity occurred?

                   

                  I will follow up shortly.

                   

                  Cheers

                  Mark

                  • 6. Re: Filtering based off calc field
                    James Linnett

                    Hi Mark,

                     

                    Date/time is certainly something I can easily add into my dataset if required.

                     

                    Looking forward to your solution.

                    • 7. Re: Filtering based off calc field
                      Mark Fraser

                      Hi James

                       

                      I need to do some more work, but this is essentially the problem -

                      Each session/ cookie has a number of interactions, which happen in an order 1,2,3,4,5,6

                      The checkout review is 5

                       

                      On the false at the moment we return 0 for everything not checkout review and 5 for the check out review so we get 0,0,0,0,5,0

                       

                      You're then testing is 1 < 0, 2 < 0 etc. hence you get only after checkout, or checkout when 5=5.

                      What we instead need to do, is order the data differently, and use that to create the interaction order, I was hoping to use date/time.

                       

                      Does that make any sense?

                       

                      Cheers

                      Mark

                      • 8. Re: Filtering based off calc field
                        James Linnett

                        So far it does, yes.

                         

                        It will be interesting to see how you use date/time in this instance.

                         

                        James

                        • 10. Re: Filtering based off calc field
                          Mark Fraser

                          Hi James

                           

                          Apologies for the delayed reply.

                           

                          I have something... its currently a bit of a hack, and has a potential issue (although that depends on your data, I'll explain later).

                          I have a higher version of Desktop, but will explain the steps.

                           

                          The COR Interaction calc is fine... in the provided example we didn't need TRIM()

                           

                          IF [Page] = "CHECKOUT REVIEW"

                          THEN [Interaction]

                          END

                           

                          What we need is the resulting number, which we use to leverage the next part, for now the number is 5.

                          Then I have amended your NXT Interaction calc, to this...

                           

                          IF [Interaction] = [COR Interaction] THEN "CHECKOUT"

                          ELSEIF [Interaction] < [Parameter 1] THEN "BEFORE CHECKOUT"

                          ELSE "AFTER CHECKOUT"

                          END

                           

                          The hack is in bold, I missed a key step

                          Create a new parameter, just like this, the key, you need to Add from Field COR Interaction

                          We use the parameter to hold the '5' value, then use that to leverage what you need.

                          Output

                          The big problem with this method I mentioned earlier...

                          if you have multiple sessions, with different COR Interaction values, i.e. its not always 5, or the same, then I'm not entirely sure it will work, the parameter will have multiple values.

                           

                          I'll keep thinking... i have the idea to leverage LoD calculations, but haven't got there yet.

                          Just as a FYI, although its a fun problem to do in Tableau, I suspect that it maybe easy to do this in something like SQL, where you have more control over manipulation and sub-querying.

                           

                          Interested to see if anyone else jumps in with a better solution, normally with something like this, there is more than 1 way

                           

                          Cheers

                          Mark

                          • 11. Re: Filtering based off calc field
                            Mark Fraser

                            v9.3.5 attached - you wont be able to open with older versions, hence the above steps!

                            • 12. Re: Filtering based off calc field
                              Mark Fraser

                              Shawn Wallwork

                              Hey Shawn, wondered / hoped you may have something more elegant than my hack above?

                              • 13. Re: Filtering based off calc field
                                James Linnett

                                Hi Mark,

                                 

                                Thanks for the time and effort you have put into this solution, but unfortunately, as you have already guessed, this won't work for me.

                                 

                                I will have various interactions across thousands of sessions/cookies and 'Checkout Review' won't remain static across the customer journey.

                                 

                                Good point re SQL, I was hoping to do this action via Tableau but i'll look out of the box.

                                 

                                James

                                • 14. Re: Filtering based off calc field
                                  Simon Runc

                                  hi James,

                                   

                                  I've only had a quick read through, and it appears the dynamic nature of where the "Checkout" appears is the issue...

                                   

                                  Rather than use a parameter I've used an LoD (as Mark was alluding to) in order to dynamically pick up the checkout Interaction number for each Session/Cookie (if there are other Dimensions in your 'real' version which need to also be taken into account, then just add the to the LoD)

                                   

                                  [Checkout Interaction Value - LoD Session/Cookie]

                                  {FIXED [Session],[Cookie]: MIN([COR Interaction])}

                                   

                                  So this will for each Session/Cookie return (at row level) the Interaction that was the Checkout...we can then adapt Mark's formula to use this rather than the parameter

                                   

                                  [NXT FINAL Based on LoD]

                                  IF [Interaction] = [Checkout Interaction Value - LoD Session/Cookie] THEN "CHECKOUT"

                                  ELSEIF [Interaction] < [Checkout Interaction Value - LoD Session/Cookie] THEN "BEFORE CHECKOUT"

                                  ELSE "AFTER CHECKOUT"

                                  END

                                   

                                  Hope that does the trick, but if not let me know where it's failed and why...and hopefully we can handle for it. I've attached this in 9.3.

                                  2 of 2 people found this helpful
                                  1 2 Previous Next