3 Replies Latest reply on Sep 15, 2018 9:39 AM by Ankit Bansal

    if certain dimension filter selected then...

    jon rios

      i have a dimension that list period ID, there are monthly period ID quarterly period ID.  The quarterly Period ID do not contain data! only monthly dimensions.

       

      i want the user to select a 'Quarterly period' 20173 or 20174 , and if a quarterly period is selected, then sum up the monthly values within that period...i cannot hard code the dates since data will be updating...  any help structuring the calculated field?  thanks

       

        • 1. Re: if certain dimension filter selected then...
          Deepak Rai

          It is possible but a workbook please.

          • 2. Re: if certain dimension filter selected then...
            jon rios

            if i could add a WB i would of, i will take psuedo-code if you can, i just need some help with structure of the flow..

            my first guess is see if a quarter is selected then add up those that match the quarter, in programming its easy, but in Tableau its much more difficult (to me, thats why i am requesting help)

             

            i tried doing something like this but it doesnt work... but here is the logic...

             

            if LEN( [Period ID] = 5   then       // if quarterly is selected

             

            if right( [Period ID] , 1) = 3   then  // if 3rd quarter selected, sum up months in 3rd quarter

                 if right( [Period ID], 1) = 7 and

                 right( [Period ID], 1) = 8 and

                 right( [Period ID], 1) = 9         then

                      sum( [my measure] )  else

             

            if right( [Period ID] , 1) = 4 then    // if 4th quarter selected, sum up months in 4th quarter

                 if right( [Period ID], 1) = 10 and

                 right( [Period ID], 1) = 11 and

                 right( [Period ID], 1) = 12        then

                      sum( [my measure] )  else

            • 3. Re: if certain dimension filter selected then...
              Ankit Bansal

              Jon,

               

              Can you create a parameter from that period dimension and use that parameter as filter. Once you do that you can use below formula.

               

              if LEN( [param Period ID] = 5   then  (    

              if right( [param Period ID] , 1) = 3   then 

               

               

                   sum(if right( [Period ID], 1) = 7 OR

               

               

                   right( [Period ID], 1) = 8 OR

               

               

                   right( [Period ID], 1) = 9         then

               

               

                     then   [my measure] end) 

               

               

              elseif right( [param Period ID] , 1) = 4 then   

               

               

                   sum(if right( [Period ID], 1) = 10 or

               

               

                   right( [Period ID], 1) = 11 or

               

               

                   right( [Period ID], 1) = 12        then

               

               

                        [my measure] end) 

               

              else sum(if [Period ID] = [param period id] then [yr measure] end)

              end

               

              Syntax might not be exactly correct. Please check for that.

               

              Thanks,

              Ankit Bansal