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

# if certain dimension filter selected then...

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...

It is possible but a workbook please.

• ###### 2. Re: if certain dimension filter selected then...

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...

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