"Error when using dynamic calculation with periodOverPeriodLastValue in QuickSight"

Question & Answer
,
1

Hi everyone,

I’m trying to use the following dynamic formula to replace the period argument in the periodOverPeriodLastValue function in Amazon QuickSight:

toString(
ifelse(
${NivelData} = ‘WK’, ‘WEEK’,
${NivelData} = ‘MM’, ‘MONTH’,
${NivelData} = ‘DD’, ‘DAY’,
${NivelData} = ‘Q’, ‘QUARTER’,
${NivelData} = ‘YYYY’, ‘YEAR’,
‘MONTH’
)
)

What I’m trying to achieve is to dynamically determine the correct period (weekly, monthly, daily, etc.) based on the value of ${NivelData}.

However, when I try to use this formula in the periodOverPeriodLastValue function, I’m getting an error, and it doesn’t work as expected. Has anyone encountered something similar or have suggestions on how to fix this?

Thanks in advance for any help!

1 Like
2

Hello @matheusice12 Hope this message finds you well!!

In Amazon QuickSight, the periodOverPeriodLastValue function does not support dynamic expressions directly as an argument for the period parameter. The period parameter must be a static value such as 'WEEK', 'MONTH', 'DAY', etc. To address this , I suggest the following approach:

Suggested Solution: Separate Calculations for Each Period

  1. Create Separate Calculations for Each Period
    Define individual calculations for each period you wish to use. For example:

    -- For WEEK
periodOverPeriodLastValue({YourMeasure}, 'WEEK')

-- For MONTH
periodOverPeriodLastValue({YourMeasure}, 'MONTH')

-- For DAY
periodOverPeriodLastValue({YourMeasure}, 'DAY')

-- For QUARTER
periodOverPeriodLastValue({YourMeasure}, 'QUARTER')

-- For YEAR
periodOverPeriodLastValue({YourMeasure}, 'YEAR')

  2. Use Conditional Logic to Select the Appropriate Calculation
    Combine these calculations using an ifelse statement to dynamically select the correct calculation based on the value of ${NivelData}:

    ifelse(
    ${NivelData} = 'WK', periodOverPeriodLastValue({YourMeasure}, 'WEEK'),
    ${NivelData} = 'MM', periodOverPeriodLastValue({YourMeasure}, 'MONTH'),
    ${NivelData} = 'DD', periodOverPeriodLastValue({YourMeasure}, 'DAY'),
    ${NivelData} = 'Q', periodOverPeriodLastValue({YourMeasure}, 'QUARTER'),
    ${NivelData} = 'YYYY', periodOverPeriodLastValue({YourMeasure}, 'YEAR'),
    null
)

Unfortunately, it is not possible to pass a dynamic expression directly as the period argument. This manual approach is necessary to handle different periods dynamically.
If you believe this functionality should be supported, I recommend reaching out to AWS Support or providing feedback to suggest enhancing the flexibility of this function in future updates.

Please, let me know if it helps you

3

Thank u <3 worked for me this solution