Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
Thanks a lot, you are a star
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])
I am not 100% confident, have you tested to see if it works or not?
its not working with autoCalendar.Data
Then I guess it is a problem related to derived fields