2 Replies Latest reply on Dec 23, 2016 5:57 AM by Corey Brown

Action Filter for Rolling Time Period

I was able to calculate one number for average@@@@@ employee count for Current Month, R3, R6, and R12 in one table.  See the image below.

The logic behind this Employee Count calculation is as follows:

if attr([Time Period]) = "Current Month" then COUNTD([Employee Number]) else

if attr([Time Period]) = "R3" THEN WINDOW_SUM(SUM([Technician Count]), first()+1, 0)/3

ELSEIF  attr([Time Period]) = "R6" then WINDOW_SUM(SUM([Technician Count]), FIRST()+1, 0)/6

elseif  attr([Time Period]) = "R12" then WINDOW_SUM(SUM([Technician Count]), first()+1, 0)/12

END

END

I would like the ability to drill down via action filter to show the employees that make up the Current Month, R3, R6, and R12 averages.  My problem arises when I click on the action filter for R12 for example, the destination sheet only shows me the first 6 months of the 12 months needed.  Likewise, when I click the R6 action, I only get the first 3 months of 6 months needed.  I think this problem occurs because I am using an IF statement to define my time periods, therefore the order of operations is what leaves me with the wrong number of months.  Is there any way around this?  Any help would be much appreciated!  Attached is an example workbook I put together in Tableau Version 10.

• 1. Re: Action Filter for Rolling Time Period

Hi Corey

Because "If then elseif" is working like that

Once first met condition is set, second condition will not be apply. so, R3 is R3 and not R6 or R12.

To allow duplicated count, I used Union. (three table prepared).

Because all the measure is "countd" duplication does not matter foe this specific case.

[Time Period]

IF DATETRUNC('month',[Date]) = DATETRUNC('month',TODAY()) THEN 'Current Month'

[Employee Count Window Sum _SM]

if attr([Time Period]) = "Current Month" then COUNTD([Employee Number])

elseif attr([Time Period]) = "R3" THEN SUM([Employee Count])/3

ELSEIF  attr([Time Period]) = "R6" then sum([Employee Count])/6

elseif  attr([Time Period]) = "R12" then sum([Employee Count])/12

END

Thanks,

Shin

• 2. Re: Action Filter for Rolling Time Period

Thanks for the response Shinichiro!  Unfortunately, a union will not work because in my real situation, the data source is a large server extract.

Corey Brown

Office  (501) 570-3172

Mobile (501) 517-0572

I care about providing an outstanding customer experience.