topic Unable to to sum based on current year vs last year to date in App Development

Unable to to sum based on current year vs last year to date

Anonymous — Wed, 18 Apr 2018 09:33:40 GMT

Hi, I am new to QS. I have data for year 2017 & 2018 in a table. what i am try to do is take SUM of all transactions up to TODAY but only for current year. I am trying to prepare comparison of current YTD vs Last YTD .

I have tied all sort of YTD & LY TD formulas Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year) but none seems to be working

Attached is my detailed ! .QVF file

Re: Unable to to sum based on current year vs last year to date

sunny_talwar — Wed, 18 Apr 2018 12:03:03 GMT

Is this the goal?

Expressions

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}>} [Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}, MPECode={'1. JA JABH'}>}[Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE), -1)) & '<=' & Date(AddYears(Max(DATE), -1)))"}>}[Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE), -1)) & '<=' & Date(AddYears(Max(DATE), -1)))"}, MPECode={'1. JA JABH'}>}[Total Avail Room])

Re: Unable to to sum based on current year vs last year to date

Anonymous — Wed, 18 Apr 2018 12:31:25 GMT

Thanks a lot, you are a star

Re: Unable to to sum based on current year vs last year to date

Anonymous — Wed, 18 Apr 2018 13:08:10 GMT

just for my understanding.

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}>} [Total Avail Room])

DATE (capital) is my date field? and I can also replace it with my auto calendar field as below?

=Sum({<[Date.autoCalendar.Date] = {"$(='>=' & Date(YearStart(Max([Date.autoCalendar.Date]))) & '<=' & Date(Max([Date.autoCalendar.Date])))"}>} [Total Avail Room])

Re: Unable to to sum based on current year vs last year to date

sunny_talwar — Wed, 18 Apr 2018 15:24:28 GMT

I am not 100% confident, have you tested to see if it works or not?

Re: Unable to to sum based on current year vs last year to date

Anonymous — Thu, 19 Apr 2018 06:18:40 GMT

its not working with autoCalendar.Data

Re: Unable to to sum based on current year vs last year to date

sunny_talwar — Thu, 19 Apr 2018 14:00:04 GMT

Then I guess it is a problem related to derived fields