Accumulate all values up to the selected filter

[mwittmann]

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

[vinieme12]

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-oth...

[mwittmann]

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...

[vinieme12]

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

[mwittmann]

Expression: 

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

Data Modell: 

Thank you!!

[mwittmann]

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

[vinieme12]

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/

[mwittmann]

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...

[vinieme12]

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/

[mwittmann]

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');