Can we do Sumifs or Use ifelse with sumif

Question & Answer
,
2

Hi @Cindy

To achieve the desired revenue data per quarter while handling Q1 differently from other quarters, you can use a combination of the IF function and the PeriodOverPeriodDifference function in Amazon QuickSight. Here’s how you can do it:

  1. First, create a calculated field to calculate the YTD revenue using the logic you provided in your FYI section:
ifelse(
    (year=2023 and compplantypegroup="QUOTA") or (year=2023 and compplantypegroup="HYBRID"),
    {tcv_usage_ytdrevenue} + revenue,
    (year<2023 and compplantypegroup="QUOTA") or (year<2023 and compplantypegroup="HYBRID"),
    revenue,
    NULL
)

This will calculate the YTD revenue based on the conditions you specified.

  1. Next, create another calculated field to handle the Period over Period Difference. Use the PeriodOverPeriodDifference function to calculate the difference between the current quarter’s revenue and the revenue of the previous quarter for all quarters except Q1:
ifelse(
    extract("MM", {date}) != 1,  -- Check if it's not Q1
    PeriodOverPeriodDifference(sum({YTD_Revenue}), 1, 1),  -- Calculate period over period difference for all quarters except Q1
    sum({YTD_Revenue})  -- For Q1, simply use the YTD revenue as is
)

In this formula, we are using the extract function to check if the month part of the date is not equal to 1 (i.e., not Q1). If it’s not Q1, we calculate the period over period difference using the PeriodOverPeriodDifference function with a period length of 1 (i.e., one quarter) and offset of 1 (i.e., previous quarter). Otherwise, for Q1, we simply use the YTD revenue as is.

Now, you can create a visualization using the new calculated field for period over period difference, and it will handle the Q1 data differently while providing the desired revenue data per quarter for multi-years.

1 Like