9 Replies Latest reply on Nov 8, 2013 12:34 PM by Steve Gesuale

# calc for YTD/last day entered

Hi,

I am tracking daily sales against daily targets blended from two workbooks in which targets are pre-entered through the end of the year and sales are entered daily – but usually a few days behind the actual day. I’ve got bullet charts using YTD and MTD calc’s but I want to change TODAY() to be not “today” but the last day of sales entered.

IF[Date]<=TODAY() and DATEDIFF('year',[Date],TODAY())=0 THEN [Target] END

IF[Date]<=TODAY() and DATEDIFF('year',[Date],TODAY())=0 THEN [Sales] END

Any suggestions for revising this so it returns YTD through the last day of Sales entered, rather than “today”?

Thanks as usual!

Steve

• ###### 1. Re: calc for YTD/last day entered

Hi Steve,

If you want to show the sales of the latest Sales date not today then you can use the following formula. If this is not requirement, let me know & also attach a sample packaged workbook.

if attr([date])=window_max(attr[Date]) then attr([Sales]) else null end

Warm Regards,

Prashant Sharma - India | LinkedIn

1 of 1 people found this helpful
• ###### 2. Re: Re: calc for YTD/last day entered

HI Prashant,

Thanks for the help, but I wasn’t able to get that to work. Was that for a table calculation?

Attached is the workbook, as you suggested. See the bullet charts. What I am trying to do is to revise them so that they display - rather than YTD and MTD for the Target amount – year-to-last-date-entered and month-to-last-date-entered. The last date entered is Oct 14th so the target amount would be based on that, not TODAY().

Steve

• ###### 3. Re: calc for YTD/last day entered

Use this:

IF attr([Date])<= window_max(attr([Date])) and (DATEDIFF('year',attr([Date]),window_max(attr([Date]))))=0 THEN sum([Target]) END

This might help. Use same formula for Sales too. This is in calculated field.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 4. Re: Re: calc for YTD/last day entered

Hi Prashant.

I created calculated fields based on what you sent but I must be doing something wrong. In the attached workbook, they seem to be treated as table calc's?

Thanks again for any help.

Steve

• ###### 5. Re: calc for YTD/last day entered

Hi Steve,

After 8 days you replied . I am on a holiday for a week, so not able to check & send you workbook. Now, follow these steps:-

1) Create a parameter using this field & in parameter choose "Range" & check Maximum. Remember to choose values from this Date field only. If values are not same as your Date fields, please again set values from option "Set from field".

2) Now create the formula

IF[Date]<=[Date Parameter] and DATEDIFF('year',[Date],[Date Parameter])=0 THEN [Target] END

IF[Date]<=[Date Parameter] and DATEDIFF('year',[Date],[Date Parameter])=0 THEN [Sales] END

May be this will help. If not let me know. If i am not available then someone from community will help.

Warm Regards,

Prashant Sharma - India | LinkedIn

1 of 1 people found this helpful
• ###### 6. Re: calc for YTD/last day entered

Great - thanks; that's very helpful. Was traveling also thus the slow response.

Steve

• ###### 7. Re: Re: calc for YTD/last day entered

Dear Prashant,

Thanks again for your kind help. I think I almost have it, but I may have set up the Date Parameter incorrectly, as the calculated fields are only pulling one day rather than Jan 1 to Last Day Entered. Would you mind having a look at the workbook attached?

Appreciate any help and warm regards,

Steve

• ###### 8. Re: Re: Re: calc for YTD/last day entered

Hi Steve,

Check the attached file.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 9. Re: Re: Re: calc for YTD/last day entered

Hi Prashant,

Great! Now I understand. Thanks again for your kind help.

Warm regards,

Steve