6 Replies Latest reply on Nov 3, 2017 7:57 AM by Joe Oppelt

    Parameter(s) in a case statement?

    Joshua Preston

      I'm counting up # of demos and # of labs and trying to those figures in the title of my dash by using a parameter to change them dynamically.

      I created a parameter and calc field to get the # of demos working but can't nail down how to get # of labs working. Are there some parameter wranglers who can help?


      10.4 twbx attached


      SCREENSHOT has calc field that powers the # of demos.


        • 1. Re: Parameter(s) in a case statement?
          Peter Fakan

          Hi Joshua,


          I'm not 100% sure of your requirements, but I have reconfigured your parameters to work off calculated fields, where you had it the other way around. I only did the first five, but this should give you the gist.


          Step 1 - Create demos or labs parameter

          Step 2 - Create demos or labs calculated field to display desired measure

          Step 3 - create calculated fields for your next parameter - note the use of the [demos or labs] calculated field within this calculated field


          These will likely end up in a parameter control too, but I wasn't sure where you were going with this...


          HTH 10.4 attached



          1 of 1 people found this helpful
          • 2. Re: Parameter(s) in a case statement?
            Joe Oppelt

            (10.4 here)


            That's a more complicated way to compute that value than needed, but it works.  You could do the same thing for [CASE Labs] and then use that.


            But I used LOD to calc the value.  See Calculation1 (for Demos) and Calculation2 (for Labs) in the attached.  I plunked it into tooltips, but you can use it in the title as well.  One calc, no matter what param value the user selects.  And you can throw away the # of Demos param.

            • 3. Re: Parameter(s) in a case statement?
              Joshua Preston

              Thanks Joe for sending along this calculation. It's super efficient and it worked!


              Followup: I tried creating a CASE Labs calc field but ran into issues. Did you mean that it's possible to create this similarly to the CASE Demos calc field? I'm missing one or two pieces to the puzzle. Any chance you can replicate a CASE Labs field that works using that method? (I'd like to develop a better mastery of the method I started with for practice) much appreciated. Josh

              • 4. Re: Parameter(s) in a case statement?
                Joe Oppelt

                I had to dig into what was really happening in that CASE statement for demos.


                That calc works because you have a 1:1 correspondence between rows and demo titles.


                For LABS, you have multiple copies of a lab name under each research name.  (Thus, you have multiple demos per lab in many cases.)

                So the CASE logic actually won't work for Labs.


                At first I tried making the CASE by excluding null values of lab name.  And then by excluding lab name = " ".  I had to make sheet 2 to really see what the data looks like.  And that's when I realized that a lab applies to multiple demos.


                So I abandoned the whole CASE logic, and did another LOD calc to count up how many dups of a given lab there are under any study.  (See calculation3.  Notice that I subtracted 1 from the sum.  Every occurrence of a lab will have at least one occurrence.  Since I'm counting dups, I subtract the first occurrence.)


                Now in [CASE labs] I take the sum of demos, and subtract out the sum of duplicates, and that gives me the number of labs.


                Note:  Your CASE approach will fail if ever you have duplicate demo names under a given research area.

                • 5. Re: Parameter(s) in a case statement?
                  Joshua Preston

                  Thanks for digging into this Joe. You answered my main question about why a CASE Labs statement couldn't simply replicate the CASE Demos statement.  (multiple copies of a lab name under each research name.) Thanks for explaining the alt approach and how it works. This will help me tackle future projects similar in nature. The workbook wasn't attached so I couldn't see calc 3, but I'd love to see the approach. thx again.



                  • 6. Re: Parameter(s) in a case statement?
                    Joe Oppelt

                    I see it attached to m last reply, but here it is again.