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 | ||||
Can you post some sample data in Excel?
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
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)
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?
You also need to replace YYYY-MM-DD to match the date format set in your application
Hello,
thank you for your detailed help. I got it now!😄
kindly close the thread by marking a response as solution