Sumif or ifelse problem with date condition

Question & Answer
,
1

Hi,

I’m trying to have a bar chart for equity movement by year.

for example, the equity movement 2023 equity - 2022 equity.

I created calculated filed for the equity movement like this:

// CY
(
    ifelse(
         {year} = extract('YYYY', ${DateIn})
        AND Date = ${DateIn}
        AND in({Category}, ["EQUITY"]),
        {amount},
        0
    ) 
)

- 

// PY

(
    ifelse(
                {year} = extract('YYYY', ${DateIn}) - 1
            AND in({Category}, ["EQUITY","PROFIT & LOSS ACCOUNT"]),
            {amount},
            0
    ) 
)

The calculated field is giving the expected result when presented in a pivot table, without having the year as row or column.

image
image853×217 9.2 KB

Now, the problem when I add the year in the visual, pivot table or bar chart or any, the calculation will give a wrong result, because having the year will give PY as 0 in 2023 and CY will give 0 in 2022.

I tried creating a calculated field for each year, and list them in a table, again, once I add the year as row, it will effect the ifelse.

So, what I need is to sum values from different years and have them listed for each year.
Do you have any solution for that?

2

If you leverage Quicksight’s built in custom SQL you could try something like what I’ve attached below:

SELECT 
  CurrentYear.year,
  SUM(CurrentYear.equity) - SUM(PreviousYear.equity) AS EquityMovement
FROM 
  YourDataTable AS CurrentYear
LEFT JOIN 
  YourDataTable AS PreviousYear 
  ON CurrentYear.unique_id = PreviousYear.unique_id
  AND PreviousYear.year = CurrentYear.year - 1
GROUP BY 
  CurrentYear.year
3

Hi @todd.hoffman
I did it in the SQL, but the problem is my dataset is too big, and we have legacy data for many years!

I’m looking to check if we can do it through calculated field, is this possible?

4

Hello @haythoo, if the SQL statement update that @todd.hoffman suggested isn’t the route you want to take, you could try utilizing the LAC-W function sumOver() that is built into QuickSight. This allows you to sum the amounts by category and year groupings as the partition level.

I will mark this as a solution for now, but if you have any follow-up questions on implementing the calculated field please let me know!