Can we do Sumifs or Use ifelse with sumif

Question & Answer
,
1

Hi ,

I want to achieve below calculations:
I currently only have YTD quarterly data. Need to get the revenue data per quarterly by using YTD revenue- previou quarter for multi years.
I can use the period over period difference to get it. But at Q1 each year, I need to simply use Q1’s data instead of using Q2-Q1 . How to achieve it?

image
image803×69 1.74 KB

FYI: The YTD revenue data is using: 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)

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