14 Replies Latest reply: Apr 25, 2018 9:47 AM by Mark Little

Calculation Date

Hi,

I'm trying to work out a YTD calculation but it doesn't work and just totals the whole year, any idea why please?

Thanks

• Re: Year to Date

I Believe you're missing the } to finish off set analysis, try the below:

Sum({<date_start={">=\${(=yearstart(max(date)))<=\$(=max(date))"},year=,month=> } )Total_amount

• Re: Year to Date

May be this -

Sum({<date_start={">=\$(=yearstart(max(date)))<=\$(=max(date))"},year=,month=>}Total_amount)

• Re: Year to Date

try this may be -

Sum({<date_start={">=\$(=yearstart(Date(max(date),'DD/MM/YYYY')))<=\$(=Date(max(date),'DD/MM/YYYY'))"},year=,month=>}Total_amount)

• Re: Year to Date

Digvijay is right, this should work:

Sum({<date_start={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}, year=, month=>} Total_amount)

• Re: Year to Date

if you try the following in a text box and se if you get the correct date formats out?

yearstart(max(date))

max(date))

• Re: Year to Date

Looks like your second limit is not returning current month. I would use :

Sum({<date_start={">=\$(=date(YearStart(Max(date)),'DD/MM/YYYY'))<=\$(=date(Today()),'DD/MM/YYYY')"}, year=, month=>} Total_amount)

This return todays date as the second limit, so from 01/01/2018 to 25/04/2018.

• Re: Year to Date

What does what Sasidhar told return?

If you are getting correct values and no 0 you could try this:

Sum({<date_start={">=\$(=date(YearStart(Max(date)),'DD/MM/YYYY'))<=\$(=date(Max(date)),'DD/MM/YYYY')"}, year=, month=>} Total_amount)

• Re: Year to Date

If you do 2 text boxes, one with yearstart(max(date)) and another with max(date)) what do you get on them?

• Re: Year to Date

Sum({<date_start={">=\$(=YearStart(Max(date_start)))<=\$(=Max(date_start))"}, year=, month=>} Total_amount)

Not working?

Do you have data different from 0 for the Total_amount field and the definied dates, right?

What selections do you have? Any issue with the year and/or month fields?

Please share and example so we could provide you the correct formula.

• Re: Year to Date

Is the date format of DATE_START  is the same as returned by your expressions? ie DD/MM/YYYY format?

• Re: Year to Date

If you do daily reload I strongly suggest to create a flag in the load script and then use this flag in the set analysis

• Re: Year to Date

We do YTD like this:

SUM({< Year={'\$(vMaxYear)'}, SalesDate={'<=\$(vMaxDate)'} >}Sales)

The two variables referenced, vMaxYear and vMaxDate, are simply max expressions.

You can then do PYTD and a YOY variance like so:

SUM({< Year={'\$(vMaxYear)'}, SalesDate={'<=\$(vMaxDate)'} >}Sales)

-

SUM({< Year={'\$(vPriorYear)'}, SalesDate={'<=\$(vPriorYearDate)'} >}Sales)

The prior year variables are just the max variables subtracting a year.

For example, vPriorYearDate:  =date(addyears(vMaxDate,-1),'DD/MM/YYYY')

• Re: Year to Date

would you be able to give a sample to the document you are using. most of the suggestions should have worked, it would be interesting to figure out what the issue might be, Thanks.

• Re: Year to Date

Hi Thomas,

From a quick look at the thread it looks like you are trying to use the functions Max() on your date field and you probably have furture Dates in the field.

When doing calculation like this i like to flag them in script to simplfy the Set analysis.

So soemthing like this.

IF(YEAR(TempDate)= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_CAL_YTD,

Then your set analysis would just be

SUM({<F_CAL_YTD={1}>}Sales)

Using similar logic you add a number of different flags different date restrictions.

Mark