1 2 3 Previous Next 38 Replies Latest reply on Jul 18, 2018 6:55 AM by Shinichiro Murakami

# Cumulative sum calculated field

I have below fields in the datasource.

[Report Year] = [2016, 2015, 2014, 2013, 2012]

[Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12]

[Order Date] = different dates

[Profit] = [123, 456, 789] etc..

I want to put a where clause (filter) such that.

I want to do a max on 'Order Date' then take the month out of it. calculate previous month from it.

Pass cumulative months i.e from Jan to previous calculated month to [Report Month] if the [Report Year] = Current Year else Pass [Report Month] = Jan to Dec

e.g Max of Order Date 9/25/2016 then Month = 09 then previous month = 08. So now pass Report Month = 01 to 08 cumulative (fetch Profit for 2016 only for 8 months )  if report year = current year which is 2016 else pass Report Month = 01 to 12 cumulative (fetch Profit for prior years only for 12 months)

If this is unclear I will try putting dummy data and put a twbx

• ###### 1. Re: Cumulative sum calculated field

Pick up the the latest date in the data

[Last Date]  // LOD    Overview: Level of Detail Expressions

{fixed:max([Order Date])}

Pick up the last date of previous month

[previous Month last date]    //  Date Functions

date(datetrunc('month',[Last Date]))

Sum up to previous month last date only

[Year to previous Month]

sum(if[Order Date]<=[previous Month last date] then [Value] end)

You only need to consider the latest year to cut the latest month.

Other year, you can sum up the value just as usual.

So, what needed was to filter out the date from the latest month's first date to the last date.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Cumulative sum calculated field

Looks like due to bad data I cannot use max(orderdate). I get a future 2028 year date

So I think I will use current date which is NOW()

[previous Month last date]    //  Date Functions

date(datetrunc('month',now())) this gives me 9/1/2016

If I put below calculated filed as filter then it gives me all 'null'

[Year to previous Month]

sum(if now()<=[previous Month last date] then [Value] end)

How can I use [Report Month] if that is easy to handle to restrict Jan to Aug months for current year else all months

Also I am using 9.2 Tableau and cannot open your twbx

• ###### 3. Re: Cumulative sum calculated field

You can use parameter to control last month of the report.

[Previous month last date]

date(datetrunc('month',[Parameter 1]))-1

Thanks,

Shin

• ###### 4. Re: Cumulative sum calculated field

I have tried to mimic my data but looks like still it is little different but still gives what I am looking for.

I have 'Year' filter for end user to select.

If user selects current year which is 2016 then based on current date(which is 9/28/2016) I want to get 'LossGain' only upto end of previous month i.e Jan to Aug for 2016 but if they select any prior year I want Jan to Dec cumulative Sum

Of I remove 'Report Month' my data should roll up for 'Year' but I think this is issue way I formatted my dummy data.

Attached twbx in 9.2

• ###### 5. Re: Cumulative sum calculated field

Year filter does not matter.

You can directly compare to today().

Seems your data does not have date data, I can create below formula.

[Filter]

(DATEPARSE("yyyyMMdd",[Report Year]+right(str(100+int([Report Month])),2)+"01")-1)

<(datetrunc('month',today())-1)

Only show "True"

Thanks,

Shin

• ###### 6. Re: Cumulative sum calculated field

Getting below error.

My 'Report Year' & 'Report Month' are dimensions.

• ###### 7. Re: Cumulative sum calculated field

Dont see DATEPARSE option.

Also 'Report Year' & 'Report Month' seem to be Integer(Dimensions)

• ###### 8. Re: Cumulative sum calculated field

I just used your data but anyways, use int() or str() tow switch string and integer vise versa.

Thanks,

Shin

• ###### 9. Re: Cumulative sum calculated field

Looks like DATEPARSE is not an option for me

• ###### 10. Re: Cumulative sum calculated field

[Filter]

then "Hide" else "Show" end

Thnaks,

Shin

1 of 1 people found this helpful
• ###### 11. Re: Cumulative sum calculated field

Shinichiro Murakami you rock !!!

• ###### 12. Re: Cumulative sum calculated field

Shinichiro Murakami

Moment I add this filter = show, my alignment of output shifts

I have exact same formatting

• ###### 13. Re: Cumulative sum calculated field

Could you attach your packaged workbook?

Thanks,

Shin

• ###### 14. Re: Cumulative sum calculated field

I tried creating dummy data but was not fully able to recreate my issue but you will see portion of my issue.

Though its fir entire view its coming in corner.

Attached twbx

1 2 3 Previous Next