Hello AWS QuickSight Community,
I am working on an analysis in AWS QuickSight where I have a dataset containing the following fields: resourceId
, periodId
, periodwithdate
, department
, industry
, sector
, and levelgroup
.
I have created a horizontal bar graph with levelgroup
on the Y-axis and the percentage of resources on the X-axis. To calculate the percentage, I am using the following calculated field : distinctCountOver({res_id}, [levelGroup], PRE_AGG) / distinctCountOver({res_id}, , PRE_AGG). Additionally, I have created parameters for periodwithdate
, department
, industry
, and sector
fields. These parameters are used in filters, and I have added controls in the UI to allow users to select the required period, industry, offering, etc.
The issue I am facing is that when users select multiple periods, the percentage calculation considers all the selected periods. However, I only want to calculate the percentage for the latest period selected by the user. For example, if the user selects the following periods in the UI:
- FY25 P1 (6/2-6/29)
- FY25 P2 (6/30-7/27)
- FY25 P3 (7/28-8/10)
The calculation should only consider the data for FY25 P3.
I have tried creating calculated fields to identify the latest period and filter the data accordingly, but it is not working as expected. Here are the steps I followed: step 1 . Created a calculated field to identify the latest period:
latest_period = maxOver({periodId}, , PRE_AGG)
step 2. Created a calculated field to check if the current period is the latest period: is_latest_period = ifelse({periodId} = {latest_period}, 1, 0)
step 3. Created the percentage calculation for the latest period: distinct_count_latest_period = ifelse(
{is_latest_period} = 1,
distinctCountOver({resourceId}, [levelGroup], PRE_AGG) / distinctCountOver({resourceId}, , PRE_AGG),
0
), Now the value which is coming is zero.
Could someone please guide me