Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar

Is this the goal?

Capture.PNG

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])

View solution in original post

6 Replies
sunny_talwar

Is this the goal?

Capture.PNG

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])

Anonymous
Not applicable
Author

Thanks a lot, you are a star

Anonymous
Not applicable
Author

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])

sunny_talwar

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

Anonymous
Not applicable
Author

its not working with autoCalendar.Data

sunny_talwar

Then I guess it is a problem related to derived fields