Data set calculation and filter

[Asfak]

In QuickSight, how to show only those employees who have incentive hours greater than 0 for the latest fortnight, but at the same time show all their historical incentive hours (including null or 0 or >0).

Goal:

1. Only show employees with incentive hours > 0 for the latest fortnight. ie, do not show any employees who is not flagged out for the latest fortnight incentive hours > 0
2. Display their complete incentive history** (even if they had null or 0 incentive hours in the past).

If i apply a filter like Latest Fortnight Incentive > 0 in QuickSight, it would exclude the historical records where the incentive is null or 0 because the filter would apply to the entire dataset right?

I have tried below steps but failed.

1. Identify the LatestFortnightFlag
   “ifelse(endoffortnight = maxOver(endoffortnight, , PRE_AGG), 1, 0)”
2. IncentiveHoursFlag
   ifelse(LatestFortnightFlag = 1 AND IncentiveHours> 0, 1, 0)
3. Filter the IncentiveHoursFlag=1 but it will filter out all the history.

[apjvinod]

Hi @Asfak

Can you please try add another “OR” filter condition to show the data before fortnight .

Thanks
Vinod

[Asfak]

I have tried with OR condition but not working as expected.

[apjvinod]

Hi @Asfak

Are you getting any error or not getting correct results . Could you please upload sample data and sample dashboard in QuickSight Arena , for us to check further and assist .

NEW - QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile] QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, with working solutions, while responding to questions In this article, …

Thanks
VInod

[Giridhar.Prabhu]

Hi @Asfak,

I suggest you change your IncentiveHoursFlag calculated field as follows
ifelse(LatestFortnightFlag = 1, ifelse(IncentiveHours> 0, 1, 0), 1)

This shoud ensure that the IncentiveHours check happens only when LatestFortnightFlag = 1. For historical data the value will always return 1.

Then Filter IncentiveHoursFlag for value 1. That should bring in both sets of data that you desire

[Asfak]

Almost there. However, this shows employees with no IncentiveHours for the latest fortnight. Please see the screenshot below. I’m only interested in those who have >0 IncentiveHours in the latest fortnight, along with their past IncentiveHours. If an employee doesn’t have IncentiveHours in the latest fortnight, they need to be filtered out.

LatestFortnightFlag =ifelse(endoffortnight=
maxOver(endoffortnight, , PRE_FILTER),1,0)

IncentiveHoursFlag=
ifelse(LatestFortnightFlag = 1, ifelse(isNull(Unutilised) or Unutilised > 0, 1, 0), 1)

[Giridhar.Prabhu]

Hi @Asfak

The problem seems to be the computation of LatestFortnightFlag. Why does that get 1 in older dates?

Looking at 599 case where there is data in Aug 11 which is the only data for that employee and hence the highest. So, instead of defining what is Latest Fortnight from the dataset records you should define that outside of the dataset using a Parameter and/or calculated fields and use that date range to perform your checks for Incentive data as follows

IncentiveHoursFlag=
ifelse( >= FromDate and <= ToDate, ifelse( isNull(Unutilised) or Unutilised > 0 , 1, 0), 1)

[Asfak]

Thanks for your response. Tried below but no luck.
FromDate=addDateTime(-13, ‘DD’, truncDate(‘WK’, now()))
ToDate=truncDate(‘WK’, now())

LatestIncentiveHoursFlag=
ifelse( endoffortnight >= FromDate AND endoffortnight <= ToDate, ifelse( isNull(Unutilised) or Unutilised > 0 , 1, 0), 1)

[Giridhar.Prabhu]

Hi @Asfak

When the employee does not have data for particular dates the LatestIncentiveHoursFlag does not compute. I am not sure how to work around this issue.

Regards,
Giri

[Brett]

Hi @Asfak,
It’s been awhile since we last heard from you. Did you have any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[Brett]

Hi @Asfak,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!