Display seasonality calculation

Question & Answer
,
1

Hi there -

I have a field {verifi_non} which displays 1 or 0 depending on whether a report is compliant or not. I have another field {date}, and a field {risk} which is a variety of categories such as cars.

Could you please help me create a measure that sums up the field {verifi_non} over each month such that when a {risk} is displayed, it shows which month has had the highest sum of {verifi_non}.

Thank you

1 Like
2

Hello @Roger123, if you want to find the sum of a value with certain partitions in your data, I think the sumOver will be the best calculation to use.

The calculations would look something like this:

  1. Create a field for your date field aggregated as a month:
    Date Month = truncDate('MM', {date})
  2. Use sumOver to find the sum of verifi_non per month where the risk field is not NULL
    Sum per Month = sumOver(ifelse(isNotNull({risk}), {verifi_non}, NULL), [{Date Month}], RE_AGG)
  3. Now we can find the max value across all months, and return the month date:
    Max Month = ifelse(maxOver({Sum per Month}, [], RE_AGG) = sumOver(ifelse(isNotNull({risk}), {verifi_non}, NULL), [{Date Month}], RE_AGG), {Date Month}, NULL)

These calculations should get you close to your desired result. Please let me know if you have any further questions. Thank you!

2 Likes
3

Hi @Roger123,
It’s been awhile since we last heard from you. Did you have any additional questions or did the solution provided above from Dylan work for your case?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

1 Like