5 Replies Latest reply on Jan 9, 2020 2:24 PM by Ken Flerlage

    Highlight and Exclude using Parameter Actions

    Tommy Sims

      Hello,

       

      I was wondering if what I am trying to achieve can be done with the use of parameter actions. Below is a screenshot example and I can attach the sample workbook. I would like the report user to be able to click on a cell for a specific Week and for that cell to change color and be excluded from the Total shown. Ideally the user could select multiple weeks to be excluded. Is this possible?

        • 1. Re: Highlight and Exclude using Parameter Actions
          Jim Dehner

          Hi

          see the attached

          it is brute force but will return this

           

           

          had to play with the parameter and create a week ob date dimension

           

           

          and then this is the total sales

           

          this is the color calc

           

           

          and this is the parameter action

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Highlight and Exclude using Parameter Actions
            Ken Flerlage

            Parameter actions will only allow you to store one value--you'd only be able to filter out one week, but we could use set actions. To do this, instead of Week(Order Date), create a calculated field for the week. Then use that on the columns shelf. Then create a set based on that field.

            No need to select any values for now. Then create a set action like this:

            So, every time you click a box, it will add the week to the set. To select multiple, you can hold CTRL and then click the boxes.

             

            Next we'll create two measures--one that shows the value for each week and one that shows the overall value. Because we need to display two separate measures, we need to make sure the weekly number is always null for the grand total and the grand total is always null for individual weeks.

             

            Weekly Sales

            // Sales amount for a single week only.
            // When grand totaled, the count of weeks will be >1 so we'll get a null.
            IF COUNTD([Week])=1 THEN
                SUM([Sales])
            END
            

             

            Total Sales

            // Separate measure for the grand total.
            // Count of week will always be 1 for a given week and thus, this will return null.
            IF COUNTD([Week])<>1 THEN
                SUM(
                    IF NOT [Week Set] THEN
                        [Sales]
                    END
                )
            END
            

             

            And we'll create one more to color those fields that you've selected in the set.

             

            Color

            // Color based on whether the week is in the set or not.
            IF [Week Set] THEN
                1
            ELSE
                0
            END
            

             

            Now drag Color to the color card and change the mark type to Square. Click the color card and set to to a custom diverging palette with white on the low end and some color on the high end. Also go to the advanced settings and make sure it starts at 0 and ends at 1.

            Then drag Measure Values to the text card, then make sure Total Sales and Weekly Sales are on the Measure Values pane, like shown below:

            Now, when you CTRL click the weeks, you should get what you're looking for.

            Set.gif

            See attached.

            1 of 1 people found this helpful
            • 3. Re: Highlight and Exclude using Parameter Actions
              Tommy Sims

              Jim,

               

              Thanks so much. This was a really cool solution that I was able to recreate. The limitations that I am running into are that I cannot have no weeks selected in the view, so there will always be one value of zero. Additionally, I cannot select multiple weeks to exclude. Any ideas on how this could be achieved? I feel that it might be a limitation in the parameter action feature.

              • 4. Re: Highlight and Exclude using Parameter Actions
                Tommy Sims

                Ken,

                Thanks so much for the detailed response. It appears that Set Actions were the answer here. I was able to recreate your solution with no issues. Thanks!

                 

                -Tommy

                • 5. Re: Highlight and Exclude using Parameter Actions
                  Ken Flerlage

                  Great! This was a fun one!