How can I get first value on difference function?

스크린샷_19-8-2024_164333_ap-northeast-2.quicksight.aws.amazon.com

  • A is monthly sales value.

  • CALC_2 function is below.

difference(sum({A}), [{YM_MAGAM_BI} DESC], 1, [truncDate('YYYY', {YM_MAGAM_BI})])

What i need is that the Jan 2023 value comes in to CALC_2 column.
I tried ifelse funtion like below

ifelse(
    {FILTER_MONTH}=1, A
    , difference(sum(A), [{YM_MAGAM_BI} DESC], 1, [truncDate('YYYY', {YM_MAGAM_BI})])
)

and It comes out error ““Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.””

Is there any way to solve this problem…?

Hi @syoul

Please try the following approach to show the first value followed by the differences.

Example: (Syntax may vary)

ifelse(
    isNull(difference(sum(A), [{YM_MAGAM_BI} DESC], 1, [truncDate('YYYY', {YM_MAGAM_BI})])),
    sum(A), 
    difference(sum(A), [{YM_MAGAM_BI} DESC], 1, [truncDate('YYYY', {YM_MAGAM_BI})])
)

image

2 Likes

It works!! Thank you so much :smile:

2 Likes