Date Difference between aggregated and non aggregated field

Question & Answer
, ,
1

I have read through a couple posts relating to this, but found no solution for myself. It may be the way i am writing my calculated fields though.

For context, i am creating a report on arrears and payments
This is one of the calculated fields i am using which is an accounting period date. This is basically the date for the end of each month, based on an accounting period. This means that i can group the fields monthly.

This is the other fields i am using:
IntoArrears - returns when a PAYMENT enters arrears

image
image973×185 7.27 KB

MinArrearsDate - returns when a case enters arrears
image

I want to create a calculated field which is if we are in the current AccountingPeriodDate, do a date difference between MinArrearsDate and now(), if we aren’t in the current AccountingPeriodDate, do a date difference between MinArrearsDate and AccountingPeriodDate. What this should do is return the days in arrears as of end of each month, but if we are in the current month, it should return the current days in arrears.

How can i achieve this?

2

Hi @HarveyB-B,

You can’t use both an aggregated value (MinArrearsDate) and an unaggregated value (now) in your calculation. Try to calculate MinArrearsDate using minOver instead:

minOver(IntoArrears, [applicantid], PRE_AGG)
3

So i think my date field is also mismatched

I try this field:

ifelse({AccountingPeriodDate} = max({AccountingPeriodDate}), dateDiff({MinArrearsDateV2}, now()), dateDiff({MinArrearsDateV2}, {AccountingPeriodDate}))

For context:
MinArrearsDateV2 =
minOver(IntoArrears, [applicantid], PRE_AGG)

AccountingPeriodDate = parseDate(AccountingPeriodDateFormatStr, ‘yyyy/MM/dd’)

Look at attached Q/A at the top of this forum for breakdown of the AccountingPeriodDate field

I get the result that ifelse({AccountingPeriodDate} = max({AccountingPeriodDate}) is mismatched aggregation

4

Hi @HarveyB-B, in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)