1 2 Previous Next 21 Replies Latest reply on Jun 3, 2017 9:10 PM by Morgan DUARTE

# Monthly targets to acheive for 3 consecutive months

Hi guys,

I have a incentive scheme a bit tricky for me.

If a customer achieve his monthly target for 3 consecutive months, then he gets its quarterly target.

The monthly target is basically its yearly target /12 and the actual is updated daily for monitoring.

Any idea how to implement this?

Ideally I want to know in month 1 (if we are in Jan), how much he needs to do (remaining) to achieve its monthly target,

then if we are in the month 2, and that he passed the month 1, how much he needs, to achieve its monthly target for the month 2. If he didn't pass month 1, the blank (Or even "Failed")

then if we are in month 3 and that he passed the month 1 and 2, how much he needs, to achieve its monthly target for the month 3.

Attached a simple example.

Thank you

• ###### 1. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

I believe you are looking for this output.

As per your Sample data, Target was achieved in 1 month and there is a difference of 50 in second month similarly in 3 month.

As you mentioned Quarterly target will be summation of all three month.

So difference of Month 2 (50) and Month 3 (60) to in 3 month he has to do achieve more 110 to complete his quter target.

Use Table Calculation

Attached workbook is developed in Tableau 10.2

1 of 1 people found this helpful
• ###### 2. Re: Monthly targets to acheive for 3 consecutive months

Thanks for the quick reply Sandip,

However, they need to achieve each of the 3 months in order to get it.

it means for the customer BBB, he failed the 1st month, so it is over. Cannot compensate.

And for AAA, in Jan, it is okay, Feb, okay, March, he needs 60 more to get it.

Any way to tweak it that way?

Thank you

• ###### 3. Re: Monthly targets to acheive for 3 consecutive months

Is this correct?

• ###### 4. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

Find my approach as reference below and stored in attached workbook version 10.2 located in the original thread

1. Below/Above: if [Delta]<0 then "Below Target" else "Above Target" END

2. Delta > Edit Table calculation

Regards,

Norbert

• ###### 5. Re: Monthly targets to acheive for 3 consecutive months

Hi Sandip, the result is correct indeed. How did you do it? However any way to show that BBB fail?

Hi Norbert, thanks for sharing your idea as well. However I am looking for the result of Sandip.

Thank you

• ###### 6. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

I couple of things I might recommend are using true dates if they exist and look at joining instead of blending as it will give you more flexibility in creating LOD expressions.

That being said you can still achieve what you are looking for with the data in it's current format.  Both Sandip and Norbert provided great solutions to find the delta of customer by month against the target, so I won't go over that.

Finding if the quarter passed is a little more tricky.

1.  Create a field that returns 1 if the month met target and 0 if not:  IF SUM([Value])-[Monthly Target] >= 0 THEN 1 ELSE 0 END

2. Create a quarterly target field.  The value will be 3 always

3,  Create a Quarterly Pass/Fail field:  IF RUNNING_SUM([Monthly Pass]) = SUM([Quarterly Target]) THEN 'Passed' ELSE 'Failed' END

4.  Add the Quarterly Pass/Fail field to your worksheet and set compute to months.

FYI,  neither customer achieved their quarterly targets, it's always good to have sample data that meets more than one scenario. Let me know if you have any questions.

Regards,

Ivan

1 of 1 people found this helpful
• ###### 7. Re: Monthly targets to acheive for 3 consecutive months

Thank you Ivan,

I feel I'm getting closer.

For the Quarter passed/failed, it is a bit tricky, since let's say we are in March now and the month is not over. Customer AAA still has chances to get it.

For BBB, it is failed, since he failed at least 1 previous month in the quarter.

So, is it possible to get 3 levels? (Passed, Failed and Pending)? Or may be a totally different way to show this.

Thank you

• ###### 8. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

You could alter the Quarterly Pass/Fail field to the calculation below which might get you closer to your requirement.  Let me know if you have any questions.

Regards,

Ivan

IF RUNNING_SUM([Monthly Pass]) = SUM([Month]) THEN 'Passed'

ELSEIF MAX([Month])-1 = LOOKUP(RUNNING_SUM([Monthly Pass]),-1) THEN 'Pending'

ELSE 'Failed' END

1 of 1 people found this helpful
• ###### 9. Re: Monthly targets to acheive for 3 consecutive months

Thank you. I'm getting closer

However, when I do the sum of month, I may have several lines in the report, it will do the sum and not be equal to monthly pass.

So I need to use ATTR and I think it works...I need to test on different cases.

Thank you!

• ###### 10. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

Sorry for delayed response. I got stuck some urgent activity at our office.

Please find attached workbook as requested.

Along with Responsive tool tip.

• ###### 11. Re: Monthly targets to acheive for 3 consecutive months

Thanks Sandip for the suggestion.

However if a customer fail in Jan, it is over. Cannot compensate. So he will fail the whole quarter.

I will continue to deep dive with Ivan suggestion.

• ###### 12. Re: Monthly targets to acheive for 3 consecutive months

Hi Morgan,

Glad to hear you are getting closer.  I think ATTR will work as well as MIN or MAX.  I duplicated your data and halved the value column; the following seems to work using MAX month.  I prefer to use Min or Max rather than Attr if possible as new Tableau users are going to be familiar with min/max but probably not ATTR.  I did notice a flaw in my logic which needs to be corrected, it is possible for Month 2 to show pending which shouldn't be the case.  I created a Max Month LOD to solve for this.

Regards,

Ivan

Max Month LOD:

{ FIXED  : MAX([Month]) }

Quarter Pass/Fail:

IF RUNNING_SUM([Monthly Pass]) = MAX([Month]) THEN 'Passed'

ELSEIF MAX([Max Month LOD])-1 = LOOKUP(RUNNING_SUM([Monthly Pass]),-1) THEN 'Pending'

ELSE 'Failed' END

1 of 1 people found this helpful
• ###### 13. Re: Monthly targets to acheive for 3 consecutive months

Thank you!

This works up to month 3.

After for 4,5,6, we need to "reinitialize" the count. We don't check past performance, only 4,5,6.

If I filter the month for 4,5,6, the it doesn't work anymore. (All failed). Any idea to correct this?

Thank you

• ###### 14. Re: Monthly targets to acheive for 3 consecutive months

I'm not too sure as your sample data only included one quarter but you can try adding your month filter to context, create a quarter dimension or use a true date which would simplify things.  Tableau isn't going to know that your numeric field represents a quarter and restart for month 4,5,6.  If you can upload sample data that includes more than 3 months and I can take a look.

Regards,

Ivan

1 2 Previous Next