topic Need help with RangeSum(Above(... in App Development

Need help with RangeSum(Above(...

MindaugasBacius — Mon, 28 Nov 2022 14:00:16 GMT

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!

Re: Need help with RangeSum(Above(...

vinieme12 — Tue, 29 Nov 2022 04:02:30 GMT

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

Re: Need help with RangeSum(Above(...

MindaugasBacius — Tue, 29 Nov 2022 05:51:25 GMT

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

Re: Need help with RangeSum(Above(...

vinieme12 — Tue, 29 Nov 2022 05:58:18 GMT

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