1 Reply Latest reply on Oct 6, 2016 9:01 AM by Rahul Patel

# Help with Projections for Month with no Data

Hello All,

Need your expertise on this, as have been beating around for more than a day with No results.

Attached is the sample sheet to show you what I am trying to accomplish including my Projection Calculation utilizing Weekdays for the current month.

Little Insight:

C-suite wants to project current month utilizing what has already been done. Implying, for October, we are 2 Weekdays in (10/3 and 10/4). There are a total of 21 Weekdays in October. That leave me with 19 more weekdays to go (counting today).

So, say I have 7 Quantities ordered on 10/3 and 3 quantities ordered on 10/4, I have total of 10, thus my average for the 2 weekdays is 5. I would then multiply this with the remaining weekdays in the month, so 5 * 19, which would give me 95. I would then add what has already been done in the month (10), so my total projection for the Month would be 105 (95 Projected + 10 Actual).

Simply put, for every category, I have individual dashboard, with the respective projections as a stacked graph using Dual Axis. When I have a Category, for which no Quantity has been sold in the current month, I want the projection show of the Average of the Last 3 Months, however, if Data exist, then fall back on my calculation.

The formula below works like a charm (however, feel free to make it better or identify any issues):

//START CALC: Number of Weekdays remaining in the Month

TODAY())

,NULL

,(IIF(-DAY(MAX([Order Date]))+1<-DAY(TODAY())+1,

0,

//START CALC: WEEKDAYS LEFT IN THE MONTH

//START: TOTAL WEEKDAYS THIS MONTH

+

-

MIN(DATEPART('weekday',DATETRUNC('month',DATE([Order Date]))),6)

)

//END: TOTAL WEEKDAYS THIS MONTH

-

//START: WEEKDAYS SO FAR THIS MONTH

+

-

MIN(DATEPART('weekday',DATETRUNC('month',DATE ([Order Date]))),6)

)

//END: WEEKDAYS SO FAR THIS MONTH

// END CALC: WEEKDAYS LEFT IN THE MONTH

)

*

SUM([Quantity])

/

//START CALC: Number of Weekdays done as of yesterday

+

-

MIN(DATEPART('weekday',DATETRUNC('month',DATE ([Order Date]))),6))

//END CALC: Number of Weekdays done as of yesterday

//Adding the Actual from prior weekdays

+ SUM([Quantity])))

,0))

The Above is shown in Sheet 1 in the attached, as it shows the projections for the current month (10/2016). I have them as pages, and if you go through, you will see under Technology, there is no Data and no Projection.

To Counter the Above, I have the below, which should calculate what I need, but it does not:

(IIF(ISNULL(ATTR([Order Date])),

INT(IIF(-DAY(MAX([Order Date]))+1<-DAY(TODAY())+1, 0, WINDOW_SUM(SUM([Quantity]), LAST() - 2, LAST() )/3)),

[Projection])

)

If you go to Sheet 2, which is how I need to present and not in the Page Format, it would not show October at all.

Any help would be highly appreciated.

• ###### 1. Re: Help with Projections for Month with no Data

So after no response, and deadline approaching fast, I searched again and came across this post: Re: Projections or simple forecasts in Tableau

Jonathan Drummey does a great job to get this working. As my data set and requirements were different, I only needed 2 Iterate to get the answer. And it works flawlessly as long as the Category filter is in the Pages Section. I also have modified Iterate 1 to include my Projection field.

The moment I move it to Filter to have individual Dashboard for each Category, the Technology Dashboard would not show October Projections.

I would really appreciate some help.

Attaching the workbook (Sorry, its Version 10), however, if that does not work, I can provide a 9.3 as well.