10 Replies Latest reply on Mar 9, 2016 8:29 AM by jitan.chainani

    Merge 2 case statements into one column

    jitan.chainani

      Hi Guys,

       

      Thanks for looking at this.  If you check the source there are couple of multiple  values columns  Multi 1, Multi 2, Multi 3, Multi 4, weekend 1, weekend 2 and weekend 2 multi.

      These multiples are used to calculate estimates for week 1 and week 2.

       

      Based on these multiples I calculate the estimates and projections for next 7 days for 2 weeks only.

       

      The request is to bring Estimate Week 1 and Estimate Week 2 in one column just side by side to week 1 and week 2.

       

      So when the user selects the parameter for week 1 or week 2 the numbers update for just week 1 or week 2.

       

      Also, it would be great if i can add another projection  row based on user input by territory. i.e user should be able to enter the multiple value on tableau and the estimates and projections are automatically updated.  This could be a different row.

       

      So we have 2 rows for projections

       

      One calculated by the tableau and parameter selection and the other by user inputs. i think we will also need a new  estimate place if user decides to input their number.

       

      Note - The projections are calculated based of estimates and multiples are used to calculate estimates.

       

        • 1. Re: Merge 2 case statements into one column
          Adam Crahen

          Hi Jitan-

           

          Here is the logic for combining those CASE statements for your estimate into one column.

          2016-02-28_21-33-00.png

           

          Estimate

          IF [Week (Raw Data)] = "Week 1"

              THEN [Amount] /

                  IF [Select Multiple Week 1] = 'Weekend 1' THEN [Weekend 1]

                  ELSEIF [Select Multiple Week 1] = 'Multi1' THEN [Multi1]

                  ELSEIF [Select Multiple Week 1] = 'Multi2' THEN [Multi2]

                  ELSEIF [Select Multiple Week 1] = 'Multi3' THEN [Multi3]

                  ELSEIF [Select Multiple Week 1] = 'Multi4' THEN [Multi4]

                  END

           

          ELSEIF [Week (Raw Data)] = "Week 2"

              THEN [Amount] /

                  IF [Select Multiple Week 2] = 'Weekend 2' THEN [Weekend 2]

                  ELSEIF [Select Multiple Week 2] = 'Weekend 2 Multi' THEN [Weekend 2 Multi]

                  END

          END

           

          I am not sure I am following your next request.  If you want to clarify a bit more or provide a sample output, I'll take a crack at it.

           

          9.0 workbook attached.

          2 of 2 people found this helpful
          • 2. Re: Merge 2 case statements into one column
            jitan.chainani

            Hi Adam,

             

            Thanks for the solution. Really Appreciate your help.

             

            In the next request, basically users wants to input multiples for week 1 and week 2 to calculate estimates by themselves if they think the multiples are different.

             

            We could add one more row to the above screen shot and  a numeric parameter box on the right  so that the user can change the estimates by adding multiples by territory.

             

            They want to see database driven estimates which is already added and a user input estimates if they think the multiple should be different.

             

            Thanks for your help again,

            JC

            • 3. Re: Merge 2 case statements into one column
              Adam Crahen

              Here is what I came up with for that, maybe you can tweak it for your needs.  I added three new parameters.  When the override is on generated, it will calculate as before.  When it is on override, it will use the week1 and week2 override values.

               

              2016-02-29_15-49-23.png

              2016-02-29_15-49-48.png

              I tweaked the estimate, week1 estimate and week2 estimate formulas in this version.

               

              9.0 version again.

              2 of 2 people found this helpful
              • 4. Re: Merge 2 case statements into one column
                jitan.chainani

                Hi Adam,

                 

                Thanks again for your help. Quick question -

                 

                Can we do the override at country level? For e.g. the user wants to change just one country and not others.

                 

                Thanks,

                JC

                • 5. Re: Merge 2 case statements into one column
                  Adam Crahen

                  you could change the override from yes to the county name.  Then something like if county name equal parameter then use override figures.  But they single value parameters.

                  1 of 1 people found this helpful
                  • 6. Re: Merge 2 case statements into one column
                    jitan.chainani

                    Hi Adam,

                     

                    I am trying to work on the above logic you mentioned at country level. Created a country parameter and updated the Estimate statement.

                    I am getting it to work but it just updates for one country. and the remaining country estimate changes to Null.

                    Can you please let me know what's missing?

                    • 7. Re: Merge 2 case statements into one column
                      Adam Crahen

                      Hi Jitan-

                       

                      Unfortunately, the parameters do not have the capability for multi-select.  Please vote here: Multi-Values Parameters you will be #817. 

                       

                      I don't know how many countries are in your real data, but if it is only a handful, you could create multiple parameters and work the logic into a calculated field.  If it is many, maybe it is possible to get an override indicator in your data source?

                      2 of 2 people found this helpful
                      • 8. Re: Merge 2 case statements into one column
                        jitan.chainani

                        Hi Adam,

                         

                        Thanks for your response. I will go ahead and vote for the multi-parameter.

                         

                        One more quick question - If you look at the screen shot below I am calculating projected amount 1 based of avg. projections for new releases.

                         

                        The same calculation I do in excel and is different value.

                         

                        so for day 1 the projected amount in excel is 45,627 and tableau is calculating 443,012. (5% of 3050847(

                         

                        Would you know why different answers? Although the numbers are close.

                         

                        • 9. Re: Merge 2 case statements into one column
                          Adam Crahen

                          Hard to say without looking at the worksheet.  Looks like this version has some data blending going on where the one I just opened is joined.  It could be due the relationship - I can't tell if there are filters too.

                           

                          It could also have something to do with the # of records and the average calc, but I can't say for sure.

                          • 10. Re: Merge 2 case statements into one column
                            jitan.chainani

                            Hi Adam,

                             

                            I was able to resolve the issue. It was happening due to wrong field in the calculation.

                             

                            Thank you for your help again,

                            JC