2 Replies Latest reply on Apr 22, 2013 4:13 PM by Paul Wykoff

    Logical statement workaround for mutually exclusive results

    Paul Wykoff

      Hello everyone and thanks in advance for any and all replies,

       

      I'm trying to show a time period that has a Baseline, the last four weeks of Recent Activity, an Year to Date #, and then the Variance between the Baseline and Year to Date for a count of sales.  Normally we have the Baseline, Recent Activity, YTD, and Variance as four separate sheets.  That has worked out pretty well thus far, but is pretty inefficient when loading to Tableau Server (i.e. the more sheets you have on a dashboard the longer it takes to load). 

       

      I have considered combining the Baseline, Recent Activity, and YTD sheets into one.  The problem is the If / Case Statements that make them choose the first true value for the statement which means that a given row can't be assigned to more than one group (Baseline, Recent Activity, and YTD.  All of the Recent Activity weeks roll up into the YTD, but the calculations / logic won't let me have that same data represented twice within the same calculation/sheet (once in Recent Activity separated out by week and once in YTD rolled up together).  Any ideas on how I could work around this?  It would save 2 sheets per item I'm looking at which could really add up on a dashboard.  The code for the calculated field is below.

       

      Thanks!

       

      Paul

       

      ---Result 1 is the Baseline, Results 2-5 are Recent Activity Weeks, and Result 6 is the YTD

       

      ---Since the Recent Activity Week If Statements precede the YTD statement they get assigned the Sales rows first which means those sales/rows won't show up in the YTD #/

       

      ---I need those Sales/Rows to show up in both the YTD and Recent Activity totals.

       

      IF [FIN_WEEK_ALTERED] > [IMPLEMENT_DT]-14

      AND [FIN_WEEK_ALTERED] < [IMPLEMENT_DT] - 1

      THEN 1

       

      ELSEIF ([FIN_WEEK_DT]) >= NOW()-28 AND ([FIN_WEEK_DT]) < NOW()-21

      THEN 2

      ELSEIF ([FIN_WEEK_DT]) >= NOW()-21 AND ([FIN_WEEK_DT]) < NOW()-14

      THEN 3

      ELSEIF ([FIN_WEEK_DT]) >= NOW()-14 AND ([FIN_WEEK_DT]) < NOW()-7

      THEN 4

      ELSEIF ([FIN_WEEK_DT]) >= NOW()-7 AND ([FIN_WEEK_DT]) < NOW()

      THEN 5

       

      ELSEIF FIN_WEEK_ALTERED > IMPLEMENT_DT

      THEN 6

       

      ELSE 0 END