Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | ||||
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)
As below
rangesum(Above(sum({<DATE={"<=$(=date(Max(DATE),'DD.MM.YYYY'))"}>}Value),0,RowNo(Total)))
Refer this blog for more
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...
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
Expression:
rangesum(Above(sum({<Buchungsdatum={"<=$(=date(Max(Buchungsdatum),'DD.MM.YYYY'))"}>}Betrag),0,RowNo(Total)))
Data Modell:
Thank you!!
It's a simple example app for my accutally problem. So if this works I want to implement it into my main app.
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/
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...
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/
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');