Tableau Calculated field based on month

Question

I am trying to create a calculation in Tableau as I have in excel. I could do first half of it, but not sure how to give the condition for months. Below is how my raw data looks like.

ID Month CASE_STATUS CASE_TYPE
1011 2/1/2016 Closed Group1
1012 2/1/2016 Closed Group2
1013 3/1/2016 Open Group1
1014 3/1/2016 Closed Group3
1015 3/1/2016 Closed Group3
1016 4/1/2016 Unkwn Group1
1017 4/1/2016 Closed Group7
1018 4/1/2016 Closed Group5
1019 4/1/2016 Closed Group4
1020 2/1/2016 Open Group1
1021 3/1/2016 Closed Group1
1022 4/1/2016 Closed Group2
1023 4/1/2016 Uknwn Group1
1024 2/1/2016 Closed Group4
1025 2/1/2016 Closed Group5
1026 2/1/2016 Closed Group2
1027 3/1/2016 Closed Group3

I want to create a reference line that adds all ID's (sum of count of ID's) divide by 13 for Feb, Mar and April. This reference line will start from May and be constant for the year

field1: IIF( (month([Fiscal Month]) >1) and (month([Fiscal Month]) <5 and (year([Fiscal Month]) = 2016)) , 1,0)

field2: if sum([field1]) > 1 then round(div((count[ID]),13)) else 0 end

But this does not give me the reference line I am looking for.

I have an example image, but not sure how could I insert that here.

what's the purpose of it? Why do you calculate =sum($E3:$E5)/13 twice? Do you always want to add up the latest 3 months? Or is it hard coded always these 3 months? What kind of line do you expect and what is it supposed to mean? — Alexander
This is calculation I am using to get a reference line. the reason it is shown twice is because, once I calculate the value using feb, mar and april month, I will be using that value to show the ref line starting may for a year — Tableau Beginner

Alexander Alexander · Accepted Answer · 2016-11-25T04:34:29

It sounds like a pretty hard coded solution and for that you could just use a constant but this might help to get you started and maybe you do it dynamically later.

This is assuming that you always want to add Feb, Mar and Apr to get your reference line.

Create the [Grand Total] field with [Group1] + [Group2] + [Group3]

Create a calculated field with

SUM(
  IIF(
    [month] > '2016-01-31' AND [month] < '2016-05-01', 
    [Grand Total],
    0)
   ) / 13

call it [Reference Line]

Drag the new field onto the Details shelf, go to analytics and drag a Reference line into your graph, now you can select the new measure as the source for the reference line and it should appear with the calculated value.

If this is not what you want, it would be helpful to share a screenshot of your Tableau window and maybe a mock up of what you would like to display.

Tableau Calculated field based on month

1 Answers