1 Reply Latest reply on Nov 18, 2018 9:37 PM by Jennifer VonHagel

    month parameter should disable when i select full year

    rao marapatla

      Hi All,

       

      I have attached workbook which has 90% close to my requirement .I am looking for below 2 function on it.

       

      Requirement 1:

      when I select FY from YTD/FY parameter  months parameter has to be disabled for month selection cause FY means we are showing all months data hence wanted to disable.

       

       

       

      Requirement 2:  I need to Add  "ALL" to the parameter (I am using intergers type) by default it should show ALL(Till date here till Novemebr) for  YTD selection, when i select YTD it should show till November then based on month selection it should change  if i select YTD and month as Aug it should show Jan to aug  etc..

        • 1. Re: month parameter should disable when i select full year
          Jennifer VonHagel

          Hi Rao,

           

          How do you feel about having a single column that displays "Year" if FY is selected and "Year-Month" if YTD is selected?  If that's ok, give this is a try:

           

           

          For the Choose Month Parameter, simply add an 'All' value in the parameter list and bring it to the top.

           

          Let's create a Year/Month column that will display Year when FY is selected and Year-Month when YTD is selected.

           

          This will create an actual date field that shows 1/1/2018 for Full Year and 1/1/2018, 2/1/2018, 3/1/2018, etc. for individual months when YTD is selected.  However, we can not format this in a nice way that makes it clear we mean to show a year (2018) or a month (2018-03, for example).

           

          [Year/Month Date]:

          DATE(

            IF [YTD/FY] = 'FY'

            THEN DATETRUNC('year',[Date])

            ELSEIF [YTD/FY] = 'YTD'

            THEN DATETRUNC('month',[Date])

            END

          )

           

          So we will use that same idea, but make the Year/Month a string column so we can format it. When creating the Month label, it's good to the format yyyy-mm so that this can be correctly sorted as a string.

           

          [Year/Month]:

          IF [YTD/FY] = 'YTD'

          THEN STR(YEAR([Date])) +

              IF MONTH([Date]) >= 10 THEN '-' ELSE '-0' END +

              STR(MONTH([Date]))

          ELSEIF [YTD/FY] = 'FY'

          THEN STR(YEAR([Date]))

          END

           

          Now we need to create a Filter based on the combination of Parameters chosen to show the right records and correct some of sales in the table.

           

          [YTD Filter]:

          // IF "FY" THEN allow all data (TRUE). This will sum Sales for the entire Year. Also, when "FY" is selected, the table will show years, so it will show total year.

          IF [YTD/FY] = 'FY'  THEN TRUE

          ELSEIF [YTD/FY] = 'YTD'

                      // IF "YTD" AND Choose Month parameter = All, then allow all data (TRUE). This will show all Months and all Sales

          THEN IF [Choose Month (copy)] = 'All' THEN TRUE

                     // Otherwise if Choose Month parameter is not All, then restrict the Month to be <= the Parameter Month chosen      

             ELSE MONTH([Date]) <= INT(IF [Choose Month (copy)] <> 'All' THEN [Choose Month (copy)] END)

                     END

          END

           

          Here is the view for the Table. It's very simple and you can simply use SUM([Sales]) with no LOD.

           

          All that's left is your second requirement to disable the Month parameter if YTD/FY = FY. Tableau can't do that. I think the best option here is to use the method outlined by joe oppelt here New series of videos on swapping and popping on a dashboard.

           

          We can push the Month parameter off the dashboard when FY is selected, and push the Month parameter back onto the dashboard when YTD is selected. If you review Joe's post, you'll see how I used the sheets "Month Param Buffer 1" and "Month Param Buffer 2" to create this effect on the Dashboard. I have these colored so you can see what they're doing on the dashboard, but you'd want to make them white for the final version.

           

           

          When YTD/FY = FY, Buffer 1 pushes the parameter off the edge of the dashboard. (this won't show when published, and if the Buffer sheet is colored white it will just look like empty space.)

           

          When YTD/FY = YTD, Buffer 2 pushes the parameter onto the dashboard (and collapses Buffer 1).

           

          Workbook is attached, I hope this helps.

           

          Best,

          Jennifer