Aggregate positive yoy differences at profile level. Followed by summing up the positive yoy differences on profile level by donor type

Question & Answer
, , ,
1

I want to calculate YoY differences for each profile (Parent Fundraiser SHARE UEN or Profile ID Clean), aggregate only the positive differences for each profile, and display the total at the donor type (Parent Fundraiser_Donor Type) level for each year.

However, I am encountering challenges:

  1. Ensuring the positive YoY difference is calculated per profile before summing at the donor type level.
  2. Avoiding incorrect aggregation where the YoY difference is calculated directly at the donor type level, skipping the profile-level computation.

Pivot table Im working with:

Parent Fundraiser SHARE UEN or Profile ID Clean (Org) Parent Fundraiser name clean (Org) Parent Fundraiser team clean (Org) Parent Fundraiser Industry Clean (Org) Parent Fundraiser_Donor Type (Org) FinancialYearDonationDate donationamount Previous FinancialYearDonationDate yoy financial year donation date yoy percent financial year donation date
ABC ABC Pte Ltd null Public Agency 1/1/2022 0:00 36 40 -4
IJK IJK Pte Ltd null Public Agency 1/1/2022 0:00 36 0 36
CDE CDE Pte Ltd C2 Retail Organisation 1/1/2022 0:00 264 0 264
FGH FGD Pte Ltd C1 Food & Beverage Organisation 1/1/2023 0:00 1200 1480 -280 0.135135135
S1234567C Brie Tan C2 Retail Individual 1/1/2022 0:00 2 0 2
S2233445D Ken Yan C1 Food & Beverage Individual 1/1/2023 0:00 5000 1480 3520 0.135135135

Previous FinancialYearDonationDate =
ifelse(isNull(periodOverPeriodLastValue(sum(donationamount), FinancialYearDonationDate)), 0, periodOverPeriodLastValue(sum(donationamount), FinancialYearDonationDate))

My final output I would like is:

yoy financial year donation date (Multiple Items)
Sum of yoy financial year donation date Column Labels
Row Labels 2022 2023 Grand Total
Individual 2 3520 3522
Organisation 264 264
Public Agency 36 36
Grand Total 302 3520 3822
2

ifelse(
sum({donationamount}, [{Parent Fundraiser SHARE UEN or Profile ID Clean (Org)}])
- periodOverPeriodLastValue(
sum({donationamount}, [{Parent Fundraiser SHARE UEN or Profile ID Clean (Org)}]),
{FinancialYearDonationDate}
) > 0,
sum({donationamount}, [{Parent Fundraiser SHARE UEN or Profile ID Clean (Org)}])
- periodOverPeriodLastValue(
sum({donationamount}, [{Parent Fundraiser SHARE UEN or Profile ID Clean (Org)}]),
{FinancialYearDonationDate}
),
null
)

This is my fomula. Can assist to provide a solution please?

3

Hi @Xingyin1991,
Apologies from the community that this message went missed for so long. A quick heads up when making a post, when you add a second comment or reply to your own post, it gets removed from the New and Unanswered sections of the community making it hard for others to find and help out.

As it’s been a while since you posted this, are you still encountering the same error and looking for assistance or were you able to find a work around?

Let us know if you’re still having issues and what, if any, other solutions you’ve tried out.

Thank you!

4

Hi @Xingyin1991,
Following up here as we have not heard back in a while; did you have any additional questions or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

Hi @Xingyin1991,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!