Let's assume I have access to this data in QuickSight :
Id Amount Date
1 10 15-01-2019
2 0 16-01-2020
3 100 21-12-2019
4 34 15-01-2020
5 5 20-02-2020
1 50 13-09-2020
4 0 01-01-2020
I would like to create a boolean calculated field, named "Amount_in_2020", whose value is True when the Id have a total strictly positive Amount in 2020, else False.
With python I would have done the following :
# Sample data
df = pd.DataFrame({'Id' : [1,2,3,4,5,1,4],
'Amount' : [10,0,100,34,5,50,0],
'Date' : ['15-01-2019','16-01-2020','21-12-2019','15-01-2020','20-02-2020','13-09-2020','01-01-2020']})
df['Date']=df['Date'].astype('datetime64')
# Group by to get total amount and filter dates
df_gb = pd.DataFrame(df[(df["Date"]>="01-01-2020") & (df["Date"]<="31-12-2020")].groupby(by=["Id"]).sum()["Amount"])
# Creation of the wanted column
df["Amount_in_2020"]=np.where(df["Id"].isin(list(df_gb[df_gb["Amount"]>0].index)),True,False)
But I can't find a way to create such a calculated field in Quicksight. Could you please help me ?
Expected output :
Id Amount Date Amount_in_2020
1 10 2019-01-15 True
2 0 2020-01-16 False
3 100 2019-12-21 False
4 34 2020-01-15 True
5 5 2020-02-20 True
1 50 2020-09-13 True
4 0 2020-01-01 True
Finally found :
ifelse(sumOver(max(ifelse(extract("YYYY",{Date})=2020,{Amount},0)), [{Id}])>0,1,0)