topic Re: Accumulate all values up to the selected filter in App Development

Accumulate all values up to the selected filter

mwittmann — Mon, 16 May 2022 14:10:28 GMT

I have a problem creating a Set function for accumulating all valaues up to the selected filter (Year, Month, Date).

My aim is to SUM all values and display them in a bar char (drill down year, month, date).

The values should be summed up to the max selection.

For example: if year 2022 is selected, all values before 31.12.2022 summed up.

ID	Year	Month	Date	Value
1	2022	02	01.02.2022	50
2	2022	01	01.01.2022	50
3	2021	12	01.12.2021	50
4	2021	11	01.11.2021	50
5

Re: Accumulate all values up to the selected filter

vinieme12 — Tue, 17 May 2022 01:59:04 GMT

As below

rangesum(Above(sum({<DATE={"<=$(=date(Max(DATE),'DD.MM.YYYY'))"}>}Value),0,RowNo(Total)))

Refer this blog for more

https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

Re: Accumulate all values up to the selected filter

mwittmann — Tue, 17 May 2022 13:01:03 GMT

Thank you for your quick response. It helped me a lot. The values are accumulated properly.

The 'problem' occures using the dimensions Year and month. The accumulation is no longer correct.

I don't know how to solve this. I tried adjusting the Set - but unfortunately not successful...

Re: Accumulate all values up to the selected filter

vinieme12 — Wed, 18 May 2022 03:52:30 GMT

I think your calendar is not associated with your dataset,

please post a snapshot of your data model

and also paste here the expression you used

Re: Accumulate all values up to the selected filter

mwittmann — Wed, 18 May 2022 12:35:19 GMT

Expression:

rangesum(Above(sum({<Buchungsdatum={"<=$(=date(Max(Buchungsdatum),'DD.MM.YYYY'))"}>}Betrag),0,RowNo(Total)))

Data Modell:

Thank you!!

Re: Accumulate all values up to the selected filter

mwittmann — Wed, 18 May 2022 12:36:22 GMT

It's a simple example app for my accutally problem. So if this works I want to implement it into my main app.

Re: Accumulate all values up to the selected filter

vinieme12 — Thu, 19 May 2022 11:02:09 GMT

Check if field buchungsdatum is actually formatted a date

if the below returns 0 that means it's not correctly formatted as date

isnum(buchungsdatum)

Use the script posted in this article to create the master calendar

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Re: Accumulate all values up to the selected filter

mwittmann — Thu, 19 May 2022 14:20:02 GMT

Thank you for your response. I put in the Mastercalendar. The result of isnum(buchungsdatum) still is 0?!

I dont know what I'm doing wrong...

Showing just the "Buchungsdatum" it is correct. Also showing the year and month it's calculating woring.

Also when I selecte a year in the filter, it only show the values of this specific year, not all the values up to this year...

Re: Accumulate all values up to the selected filter

vinieme12 — Thu, 19 May 2022 15:13:56 GMT

Unfortunately I cannot open qvf files

While loading your data format the field as date

Load

Date#(Buchungsdatum,'DD.MM.YYYY') AS Buchungsdatum, OTHERFIELD, OTHERFIELD

FROM XXXX;

Try this script for calendar creation

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Re: Accumulate all values up to the selected filter

mwittmann — Mon, 23 May 2022 13:27:41 GMT

Unfortunately I can not achieve what I want to.

The problem is still, that when a selection is made (Year or month) only the values of this selection are summed up. The aim is to sum all the value up to the selection.

I also tried the other master calendar. And to use the date format function - without success.

So this is the right value for all the data.

2023 is the max year. So selecting 2023 should sum all values all. in my case only the values of 2023 are summed up

This is my loading:

Sheet1:

LOAD

Belegnummer,

Wertart,

Date#(Buchungsdatum,'DD.MM.YYYY') AS OrderDate,

Betrag

FROM [lib://Qlik Dateien (wlk_marwi)/fad26101-6639-46d2-981a-26cd43b5ab60.csv]

(txt, codepage is 28599, embedded labels, delimiter is ';', msq);

MasterCalendar:

Load

Date#(TempDate,'DD.MM.YYYY') AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('OrderDate', recno()))-1 as mindate,

max(FieldValue('OrderDate', recno())) as maxdate

AUTOGENERATE FieldValueCount('OrderDate');

Re: Accumulate all values up to the selected filter

vinieme12 — Mon, 23 May 2022 14:59:37 GMT

Can you post some sample data in Excel?

Re: Accumulate all values up to the selected filter

mwittmann — Tue, 24 May 2022 07:12:08 GMT

Belegnummer	Jahr	Monat	Wertart	Buchungsdatum	Betrag
5EP847202312000	2023	Dez.	Plan	01.12.2023	-3280000
PLAN202312ER000	2023	Dez.	Plan	01.12.2023	100
5EP847202311000	2023	Nov.	Plan	01.11.2023	-3433000
PLAN202311ER000	2023	Nov.	Plan	01.11.2023	100
5EP847202310000	2023	Okt.	Plan	01.10.2023	-3266000
PLAN202310ER000	2023	Okt.	Plan	01.10.2023	100
5EP847202309000	2023	Sep.	Plan	01.09.2023	-3448000
PLAN202309ER000	2023	Sep.	Plan	01.09.2023	100
5EP847202308000	2023	Aug.	Plan	01.08.2023	-3244000
PLAN202308ER000	2023	Aug.	Plan	01.08.2023	100
5EP847202307000	2023	Juli	Plan	01.07.2023	-2992000
PLAN202307ER000	2023	Juli	Plan	01.07.2023	100
5EP847202306000	2023	Juni	Plan	01.06.2023	-3402000
PLAN202306ER000	2023	Juni	Plan	01.06.2023	100
5EP847202305000	2023	Mai	Plan	01.05.2023	-3326000
PLAN202305ER000	2023	Mai	Plan	01.05.2023	100
5EP847202304000	2023	Apr.	Plan	01.04.2023	-3404000
PLAN202304ER000	2023	Apr.	Plan	01.04.2023	100
5EP847202303000	2023	März	Plan	01.03.2023	-3126000
PLAN202303ER000	2023	März	Plan	01.03.2023	100
5EP847202302000	2023	Feb.	Plan	01.02.2023	-2690000
PLAN202302ER000	2023	Feb.	Plan	01.02.2023	100
5EP847202301000	2023	Jan.	Plan	01.01.2023	-3517000
PLAN202301ER000	2023	Jan.	Plan	01.01.2023	100
DKILF5202212000	2022	Dez.	Plan	01.12.2022	-3232000
P100003 000	2022	Dez.	Plan	01.12.2022	2000
P1000031 000	2022	Dez.	Plan	01.12.2022	5000
P1000032 000	2022	Dez.	Plan	01.12.2022	7000
P1000033 000	2022	Dez.	Plan	01.12.2022	-10000
DKILF5202211000	2022	Nov.	Plan	01.11.2022	-3384000
P100003 000	2022	Nov.	Plan	01.11.2022	2000
P1000031 000	2022	Nov.	Plan	01.11.2022	5000
P1000032 000	2022	Nov.	Plan	01.11.2022	7000
P1000033 000	2022	Nov.	Plan	01.11.2022	-10000
DKILF5202210000	2022	Okt.	Plan	01.10.2022	-3219000
P100003 000	2022	Okt.	Plan	01.10.2022	2000
P1000031 000	2022	Okt.	Plan	01.10.2022	5000
P1000032 000	2022	Okt.	Plan	01.10.2022	7000
P1000033 000	2022	Okt.	Plan	01.10.2022	-10000
DKILF5202209000	2022	Sep.	Plan	01.09.2022	-3398000
P100003 000	2022	Sep.	Plan	01.09.2022	2000
P1000031 000	2022	Sep.	Plan	01.09.2022	5000
P1000032 000	2022	Sep.	Plan	01.09.2022	7000
P1000033 000	2022	Sep.	Plan	01.09.2022	-10000
DKILF5202208000	2022	Aug.	Plan	01.08.2022	-3199000
P100003 000	2022	Aug.	Plan	01.08.2022	2000
P1000031 000	2022	Aug.	Plan	01.08.2022	5000
P1000032 000	2022	Aug.	Plan	01.08.2022	7000
P1000033 000	2022	Aug.	Plan	01.08.2022	-10000

This is the data Im ussing

Re: Accumulate all values up to the selected filter

vinieme12 — Tue, 24 May 2022 09:57:57 GMT

Fact:
LOAD
Belegnummer,
Jahr,
Monat,
Wertart,
Buchungsdatum,
Betrag
FROM [lib://AttachedFiles/fad26101-6639-46d2-981a-26cd43b5ab60.xlsx]
(ooxml, embedded labels, table is [fad26101-6639-46d2-981a-26cd43b]);

MasterCalendar:
Load
TempDate AS Buchungsdatum,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
monthname(TempDate) As MonthYear,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Buchungsdatum', recno()))-1 as mindate,
max(FieldValue('Buchungsdatum', recno())) as maxdate
AUTOGENERATE FieldValueCount('Buchungsdatum');

exit Script;

In Chart

Dimension = MonthYear

Measure =

Accumulative sum

rangesum(Above(sum({<Buchungsdatum={"<=$(=Date(Max(Buchungsdatum),'YYYY-MM-DD'))"},MonthYear=>}Betrag),0,RowNo(Total)))

sum

sum({<Buchungsdatum={"<=$(=Date(Max(Buchungsdatum),'YYYY-MM-DD'))"},MonthYear=>}Betrag)

Re: Accumulate all values up to the selected filter

mwittmann — Tue, 24 May 2022 13:48:37 GMT

Hello thank your for your quick response! it's still not working. I followed all your instructions.

Do you have an idea why its not working?

Re: Accumulate all values up to the selected filter

vinieme12 — Tue, 24 May 2022 16:42:42 GMT

You also need to replace YYYY-MM-DD to match the date format set in your application

Re: Accumulate all values up to the selected filter

mwittmann — Wed, 01 Jun 2022 08:02:53 GMT

Hello,

thank you for your detailed help. I got it now!😄

Re: Accumulate all values up to the selected filter

vinieme12 — Wed, 01 Jun 2022 08:46:38 GMT

kindly close the thread by marking a response as solution