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

    Calculation Date

    Thomas Williams



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



        • Re: Year to Date
          Aun Ahsan

          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
            Digvijay Singh

            May be this -


            • Re: Year to Date
              Lan Liz

              Digvijay is right, this should work:

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

              • Re: Year to Date
                Sasidhar Parupudi

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




                  • Re: Year to Date
                    Aun Ahsan

                    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
                    Lan Liz

                    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
                        Lan Liz

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

                        Could you please share your document?

                          • Re: Year to Date
                            Lan Liz

                            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
                          Bruno Cavestro

                          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
                            Mark Jones

                            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
                              Mark Little

                              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



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