1

I am facing an issue I can't solve by my own.

I am working with time series. So I have date in column. The granularity can be YEAR, QUARTER & MONTH.

I wanted to know how to display the SUM of the LASTEST MONTH of the selected date granularity.

For example:

If we are displaying yearly data, I would like to see, for each year, the SUM of DECEMBER data enter image description here

If we are displaying quarterly data, I would like to the SUM of MARCH data for Q1, The SUM of JUNE data for Q2 and so on.

enter image description here

Thank you in advance for your help and feel free to ask any question if it's not clear ! :)

Regards,

Maxime

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
Max
  • 73
  • 7

1 Answers1

1

Replicating it on sample superstore

Let's assume your desired measure is [sales] . Create a CF like this

if month([Ship Date]) =
{EXCLUDE MONTH([Ship Date]) : max( MONTH([Ship Date]))}
then [Sales] END

Add this field instead of your chosen measure on the view and Voila.

enter image description here

enter image description here

enter image description here

As required in the comments, if only months at quarter ends are required, use this calculation field instead

if month([Ship Date]) =
{EXCLUDE MONTH([Ship Date]) : max( MONTH([Ship Date]))} 
and month([Ship Date]) IN (3, 6, 9, 12)
then [Sales] END

Check your Tableau version. Tableau introduced IN in its very later version.

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
  • Hi ! thank you very much for your help ! That is super useful and I learnt something. Nevertheless, it doesn't really execute what I want. In your example, for 2021, it will use January data because it s the latest available data. I would like to display Null or hide the missing data if possible. Do you an idea? :) (Btw, very nice birds pics, that's beautifull) – Max May 03 '21 at 10:46
  • Yes @Max, that is calculated in that way only. maximum of available dates. At the moment, I cannot think of a strategy to do it in Tableau the way you want. And thanks for having a look on my bird pic gallery. :) – AnilGoyal May 03 '21 at 11:03
  • Nice, I am new to Tableau so sorry if I sounds noob ! This is a nice solution but by doing this we are no longer able to see the MONTHLY data properly ^^ – Max May 03 '21 at 14:48
  • I mean, yes you see monthly data but only for Month in [3, 6, 9 ,12], the rest is NULL. So my point is, if we look at my initial question it doesn t work ! (sum the last available month for the current granularity: DEC for yearly data, MAR JUN SEP DEC for quarterly data and the month itself for monthly data !) – Max May 03 '21 at 14:56
  • I'll check it again and revert – AnilGoyal May 03 '21 at 14:58