kosta
June 19, 2023, 8:06am
1
Hi everyone,
I’m trying to create a table that shows me the last 10 results occurred in my dataset. The result column does have ‘Failure’ or ‘Success’ Values. I want that the result is shown and not the count of that result.
So I created already a rank function to get the last dates by my Checks:
rank([{Date} DESC], [Check], PRE_Filter)
now to display the last 10 result. I created 10 new calculated fields like this:
ifelse(RankDate = 1, lastValue(Result, [Date ASC], [Check]), NULL)
ifelse(RankDate = 2, lastValue(Result, [Date ASC], [Check]), NULL)
…
However it doesnt work. Because I’m getting this: ‘Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.’
What I basically want to show is:
Check, Top1_Result, Top2_Result, Top3, Top4, …, Top10
Can someone help me with my problem?
Hi @kosta - Welcome to AWS QuickSight community and thanks for posting the question. Can you please share the sample input and expected output.
Regards - Sanjeeb
kosta
June 19, 2023, 8:43am
3
Hi @Sanjeeb2022 ,
the Dataset Input looks basically like this (I hope you can read it):
Dataset
Check
Date
Result
sales_pre_1
over_1000
01.01.22
Success
sales_pre_1
over_1000
02.01.22
Success
sales_pre_1
over_1000
03.01.22
Success
sales_pre_1
over_1000
04.01.22
Failure
sales_pre_1
over_1000
05.01.22
Success
sales_pre_1
over_1000
06.01.22
Failure
sales_pre_1
over_2000
11.01.20
Success
sales_pre_1
over_2000
15.05.21
Success
sales_pre_1
over_2000
21.07.21
Success
sales_pre_1
over_2000
30.09.21
Failure
sales_pre_1
over_2000
10.12.21
Success
sales_pre_1
over_2000
30.12.21
Failure
sales_pre_1
over_5000
01.01.23
Success
sales_pre_1
over_5000
07.03.23
Success
sales_pre_1
over_5000
08.05.23
Success
sales_pre_1
over_5000
09.07.23
Failure
And this is how I want the output to be:
Dataset
Check
Top1 (Latest Check)
Top 2 (2nd Latest Check)
Top 3 (3rd Latest Check)
Top 4 (4th Latest Check)
Top 5 (5th Latest Check)
sales_pre_1
over_1000
Failure
Success
Failure
Success
Success
sales_pre_1
over_2000
Success
Failure
Success
Success
Success
sales_pre_1
over_5000
Failure
Success
Success
Success
Null
Kind Regards
Hi @Max @David_Wong - Any advise on this usecase, we can populate the dense rank and sort the data by ascending but how to display in required output?
Please help.
Regards - Sanjeeb
kosta:
ifelse(RankDate = 1, lastValue(Result, [Date ASC], [Check]), NULL)
Hi @kosta ,
Why do you need to use lastValue? Can you try the following?
ifelse(RankDate = 1, Result, null)
1 Like
kosta
June 20, 2023, 5:57am
6
Hi @David_Wong ,
if I use that, it shows me the counted result in the table and not the value ‘Success’ for example.
Kind Regards
Are you using a pivot table? If you put a text field in the Value field well of a pivot table, QuickSight does a count.
Can you try the following?
Calculate the rank.
RankDate = rank([Date DESC], [Check], PRE_AGG)
Convert the text result to an int value.
Result_Int = ifelse(Result = ‘Success’, 1, Result = ‘Failure’, 0, null)
Find the int value of the result for each rank.
Top1_Result_Int = minOver(ifelse(RankDate = 1, {Result_Int}, null), [Check], PRE_AGG)
Convert the int value back to a text field.
Top1_Result = ifelse({Top1_Result_Int} = 1, ‘Success’, {Top1_Result_Int} = 0, ‘Failure’, null)
Is this correct? Shouldn’t Top1 for over_2000 should be “Failure” and Top2 for over_2000 be “Success”?