Need help with RangeSum(Above(...

[MindaugasBacius]

Hey,

I have data like this:

I wish to provide the comparison YoY by month:

Yet I wish to show only data that belongs to:

Current - [2022-01-01 / 2022-11-28]

Previous - [2021-01-01 / 2021-11-28]

I cannot reduce the data in the backend need to do this in the frontend.

How should I accomplish this?

Thank you!

[vinieme12 · Accepted solution]

as below

Current YEar toDate

rangesum(above(Sum({<Date={">=$(=Date(YearStart(Max(Date)),'YYYY-MM-DD'))<=$(=Date(Today(),'YYYY-MM-DD'))"}>}Sale),0,rowno()))

PRevious Year toDate 

rangesum(above(Sum({<Date={">=$(=Date(YearStart(Max(Date),-1),'YYYY-MM-DD'))<=$(=Date(addyears(Today(),-1),'YYYY-MM-DD'))"}>}Sale),0,rowno()))

tmp:
LOAD date#(Date,'YYYY-MM-DD') as Date, Sale, CYTD, PYTD, Month INLINE [
    Date, Sale, CYTD, PYTD, Month
    2021-01-01, , 0, 1, 1
    2021-02-01, 1, 0, 1, 2
    2021-03-01, 2, 0, 1, 3
    2021-04-01, 3, 0, 1, 4
    2021-05-02, 4, 0, 1, 5
    2021-06-08, 5, 0, 1, 6
    2021-07-15, 6, 0, 1, 7
    2021-08-10, 7, 0, 1, 8
    2021-09-20, 8, 0, 1, 9
    2021-10-16, 9, 0, 1, 10
    2021-11-23, 1, 0, 1, 11
    2021-12-20, 5, 0, 1, 12
    2022-01-02, 2, 1, 0, 1
    2022-02-03, 2, 1, 0, 2
    2022-03-05, 3, 1, 0, 3
    2022-04-15, 2, 1, 0, 4
    2022-05-02, 2, 1, 0, 5
    2022-06-03, 2, 1, 0, 6
    2022-07-05, 3, 1, 0, 7
    2022-08-15, 2, 1, 0, 8
    2022-09-02, 2, 1, 0, 9
    2022-10-03, 2, 1, 0, 10
    2022-11-05, 3, 1, 0, 11
];

[MindaugasBacius]

Hei, vinieme12,

It works well, yet I do not get the logic laying behind it.

Why it's not working while using Set analysis with CYTD, and PYTD?

How does the ROWNO() effects the formula?

Thank you

[vinieme12]

PYTD -   this flag marks all months of the previous therefore you won't be able to use this to do a YearToDate

you can use the function inYeartodate() to mark dates from previous years but base the dates on today()

example 

InYearToDate (datefield,addyears(today(),-1),1)   as PYTD

Rowno() is needed to specify which rows to accumulate

https://community.qlik.com/t5/QlikView-Documents/Missing-Manual-Above-and-Below/ta-p/1481948