Count Range by Dimenson

[reporting_neu]

Hello,

I would like to count the number of days per year and dimension. However, I can't cope.

I have:

MDNr	ANNr	Datefield	Yearfield	Day
1	878	01.01.2024	2024	1
1	878	02.01.2024	2024	1
1	878	03.01.2024	2024	1
1	878	10.01.2024	2024	1
1	878	11.01.2024	2024	1
1	878	28.05.2024	2024	1
1	900	29.12.2023	2023	1
1	900	30.12.2023	2023	1
1	900	01.01.2024	2024	1
1	900	02.01.2024	2024	1
1	900	03.01.2024	2024	1
1	900	04.01.2024	2024	1
1	900	28.01.2024	2024	1

Comment: Day is everytime "1"

I need the following result:

MDNr	ANNr	Datefield	Yearfield	Countfield
1	878	01.01.2024	2024	1
1	878	02.01.2024	2024	2
1	878	03.01.2024	2024	3
1	878	10.01.2024	2024	4
1	878	11.01.2024	2024	5
1	878	28.05.2024	2024	6
1	900	29.12.2023	2023	1
1	900	30.12.2023	2023	2
1	900	01.01.2024	2024	1
1	900	02.01.2024	2024	2
1	900	03.01.2024	2024	3
1	900	04.01.2024	2024	4
1	900	28.01.2024	2024	5

My load script:

NoConcatenate
TMP2:
Load
MDNr
,Yearfield
,ANNr
,Datefield
,if(previous(MDNr) = MDNr and previous(Yearfield) = Yearfield and previous(ANNr) = ANNr, rangesum(peek('Countfield'), Day), Day) as Countfield
Resident TMP1
Order by MDNr, ANNr, Yearfield, Datefield ASC;

Who can help me, please?

[reporting_neu]

Yes, I did.

By ANNr or Yearfield

No separation between years

if(ANNr <> Previous(ANNr) or Yearfield <> Previous(Yearfield), 1, RangeSum(Peek('Count'),1)) as Count
...
Order by ANNr, Yearfield, Datefield;

By only Yearfield

No separation between years and ANNR

if(Yearfield <> Previous(Yearfield), 1, RangeSum(Peek('Count'),1)) as Count
...
Order by ANNr, Yearfield, Datefield;

[Kushal_Chawda]

@reporting_neu  I don't see year field in your snapshot data. If you don't have that, you need to create it

[reporting_neu]

Good point! I had the “Yearfield” field. However, there was a wrong field behind it and no year. I corrected that and now it works as it should 😃

Thank you so much! 👍