topic Re: SUM with different Date Ranges in QlikView

SUM with different Date Ranges

AjosefMD — Sat, 16 Nov 2024 18:00:01 GMT

Dear Community,

I need to sum (total) Quantities with different Date-Ranges.

In the yellow example I need for Date "06.10.2020" the sum of all Quantities between 06.10.2020 and 15.12.2020. Same for e.g. green. I need sum of Quantities between "30.11.2020" and "08.02.2021".

Maybe you have a working solution for me.

Thank you,
Josef

Re: SUM with different Date Ranges

Taoufiq_Zarra — Thu, 08 Oct 2020 10:48:21 GMT

@AjosefMD like ?

Sum({<Date={"$(= '>=' & '06.10.2020' & '<=' & '15.12.2020')"}>} Quantity)

and

Sum({<Date={"$(= '>=' & '30.11.2020' & '<=' & '08.02.2021')"}>} Quantity)

Re: SUM with different Date Ranges

AjosefMD — Thu, 08 Oct 2020 10:54:00 GMT

Yes, but the Date Range needs to be calculated new for each row and each Start-/EndDates. So I can not enter fix Dates like this.

Re: SUM with different Date Ranges

Vegar — Thu, 08 Oct 2020 11:17:44 GMT

It looks like your intervals are static, what if you use intervalmatch() to solve this this problem?

LOAD
Material,
Date,
Quantity
FROM SOURCE;

IntervalMatch (Date)
LOAD
StartDate,
EndDate
FROM SOURCE;

Re: SUM with different Date Ranges

AjosefMD — Thu, 08 Oct 2020 14:33:41 GMT

Thank you very much! It works perfect :).