How to aggregate a date field to use in a calculated field to return

Q&A
, ,
1

I have a calculated field which is an ifelse statement which checks 2 requirements (both are aggregated fields). When these requirements are met, i want to return report_date (date the data is from). However since this date is not aggregated, i get the error mismatched aggregation. Here are my fields:

report_date - date the data was captured

applicantid - the case reference (this visual is filtered to one)

case_arrears - how much arrears the case is in

PreviousDayArrears - whether yesterday was in arrears or not (returns the amount)
lag(
{case_arrears},
[{report_date} ASC],
1,
[applicantid]
)

EnteredArrearsFlag - flagged when entered arrears for a new period (based on yesterday not being in arrears):
ifelse(
{case_arrears_aggre} > 0 AND PreviousDayArrears <= 0,
1,
0
)

case_arrears_aggre is an aggregated version of case_arrears so it can be used in this calculated field:
sum({case_arrears})

image

What i want to do is replicate the EnteredArrearsFlag, but instead return the report_date. So then i have a date for each time the case enters arrears. Since this field is not aggregated, it doesn’t work.

Any ideas?

-thanks

2

Hello @HarveyB-B !

Can you share the calculated field that threw the error?

You could try something similar to the following:

ifelse(
{EnteredArrearsFlag} = 1, 
max({Date}, [{EnteredArrearsFlag}]),
NULL
)
1 Like
3

Hello, i was trying to do the EnteredArrearsFlag but return the report_date the date it matched the requirements, so would look like this:

ifelse(
{case_arrears_aggre} > 0 AND PreviousDayArrears <= 0,
report_date,
NULL
)

but since the PreviousDaysArrears is a calculated field, i don’t think i can return the report_date as its mismatched aggregation.

I have tried what you provided but got the following:

image
image938×247 8.52 KB

4

Now what i’ve done is this:

image
image765×381 4.6 KB

which is similar to what you provided (getting the max report_date)

image
image758×487 34.9 KB

5

I now have another thing i want to add, but not sure if its worth making a new post for.

I’ve mocked up what i want it to look like

image
image860×469 55 KB

Basically, while IsArrears = 1, i want to return the max(EnteredArrearsDate). So it should return what i’ve mocked up quickly. Using this, i will be able to workout days in arrears on a daily basis, however how can i return the EnteredArrearsDate (Aug 4 2024) while IsArrears = 1?

I’ve already tried ifelse(IsArrears = 1, max(EnteredArrearsDate), NULL) but getting mismatched aggregation issues

6

Hello @HarveyB-B !

I’m glad the first part of this worked out. For the second part, is {IsArrears} aggregated or a calculated field?

7

Hi there. Sorry i forgot to mention that flag.

No, its an ifelse statement which is based on whether or not case_arrears is > 0.

image

8

Hey @HarveyB-B!

Does using the same solution above not work for this as well?

2 Likes