3 Replies Latest reply on Mar 24, 2016 5:31 PM by Shinichiro Murakami

# How to calculate Quarter to Go and Year to go

Hi there,

I'm tring to build a dashboard to follow our actual revenue (Daily updated) with our monthly ambition, quarter to go and Year to go.

No problem on the monthly ambition, but it is more troublesome for our quarter target and year target.

Here is what I'm trying to calculate

Today = 24th March (Actual Revenue for March is then updated)

Jan Act = 100

Feb Act = 50

Mar Act = 70

Total = 220

Q1 Target = 300

So to achieve Q1 Target, I need a March Target of (300 - 100 - 50) = 150

But if I was in February, to achieve Q1 Target, I would have needed: (300 - 100)/2 (Remaining months) = 100

Any idea how to do such calculation with Tableau?

Attached an example of my data

Thanks a lot !

• ###### 1. Re: How to calculate Quarter to Go and Year to go

Little bit tricky...

Set parameter for testing purpose.  Assuming today() = Param Date

I created "Date" field from your "Month" field to make calculation easier.

[Month(date)]

makedate(2016,int([Month]),1)

[Date Filter] //  for the purpose of simulation with Parameter.

if [Month(date)] <= [Param Date] then [Month(date)] end

[Remained Month for Q]

{fixed [Year_Qtr]:max(month([Month(date)]))}-month([Month(date)])+1

[Run_sum]

[TGT for remained Month Q]

if attr({fixed[Year_Qtr]:min([Month(date)])})= attr([Month(date)])

then SUM([Q Target].[Quarterly Target])/attr([Remained Month for Q])

else (SUM([Q Target].[Quarterly Target])

-lookup(running_sum(sum([Revenue])),-1))

/attr([Remained Month for Q]) end

Thanks,

Shin

9.2 attached

• ###### 2. Re: How to calculate Quarter to Go and Year to go

Thanks a lot !!

Anyway to have it on the same sheet? I will need to select the MAX of Quarter or Year remaining value.

Thank you

• ###### 3. Re: How to calculate Quarter to Go and Year to go

Yes.

Create parameter and two calculated fields.

Parameter

[TGT by Param]

case  [Y,Q select]

when "Year" then SUM([Y Target].[Yearly Target])

when "Quarter" then SUM([Q Target].[Quarterly Target])

end

[Remained monthly by Parameter]

case [Y,Q select]

when "Year" then  [TGT for remained Month Y]

when "Quarter" then [TGT for remained Month Q]

END

By the way,