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?

[Kushal_Chawda · Accepted solution]

@reporting_neu  try below

Data:
Load * Inline [
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 ](delimiter is '\t');

Final:
Load *,
if(Yearfield<>Previous(Yearfield),1,RangeSum(Peek('count'),1)) as count
Resident Data
Order by ANNr,Yearfield,Datefield;

Drop Table Data;

[reporting_neu]

Many thanks for your response.

When I load your sample data it works. However, when I apply your formula to my data, I always only get "1" as "Count".

I've tried to adjust the formula several times but I just can't get the range sum right 😓

[Kushal_Chawda]

@reporting_neu  make sure that your Order by fields are in sequence as mentioned in my response. Also, make sure that your date field is in proper date format.

[Kushal_Chawda]

@reporting_neu  also make sure that field names are same in peek and actual field name

if(Yearfield<>Previous(Yearfield),1,RangeSum(Peek('count'),1)) as count

[reporting_neu]

I did something wrong when sorting. Now I get the rangesum. Thank you for that!
BUT: I also have to differentiate between the “ANNr”. That's why I tried to adapt your formula:

if(ANNr <> Previous(ANNr) and Yearfield <> Previous(Yearfield), 1, RangeSum(Peek('Count'),1)) as Count

Unfortunately, it does not differentiate between different "ANNr" numbers and does not start to form a new range for each "ANNr number (see example in the first post).

[Kushal_Chawda]

@reporting_neu  Actually you don't require condition on ANNr because your data is sorted in a way that just the condition on year is enough, but try below

if(ANNr <> Previous(ANNr) or Yearfield <> Previous(Yearfield), 1, RangeSum(Peek('Count'),1)) as Count

[reporting_neu]

I am so sorry. Now Qlik differentiates between the "ANNr" but not between the years. That's why I used "and" before.

Here is the current result:

At the turn of the year it always has to start again at 1.

[Kushal_Chawda]

@reporting_neu  did you try with or? As I mentioned as you already have year separated for ANNr, you don't really need condition for ANNr

[Kushal_Chawda]

@reporting_neu  below is working for me

Data:
Load * Inline [
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
1 1000 03.01.2024 2024 1
1 1000 04.01.2024 2024 1
1 1000 28.01.2024 2024 1 ](delimiter is spaces);

Final:
Load *,
if(ANNr<>Previous(ANNr) or Yearfield<>Previous(Yearfield),1,RangeSum(Peek('count'),1)) as count
Resident Data
Order by ANNr,Yearfield,Datefield;

Drop Table Data