Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

majidbhatti
New Contributor

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

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

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

6 Replies

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

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

majidbhatti
New Contributor

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

Thanks a lot, you are a star

majidbhatti
New Contributor

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

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

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

majidbhatti
New Contributor

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

its not working with autoCalendar.Data

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

Then I guess it is a problem related to derived fields

Community Browser