Show Top 10 Last Results as Columns

Question & Answer
, , ,
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?

2

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

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

4

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

5

Hi @kosta,

Why do you need to use lastValue? Can you try the following?
ifelse(RankDate = 1, Result, null)

1 Like
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

7

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?

  1. Calculate the rank.
    RankDate = rank([Date DESC], [Check], PRE_AGG)

  2. Convert the text result to an int value.
    Result_Int = ifelse(Result = ‘Success’, 1, Result = ‘Failure’, 0, null)

  3. Find the int value of the result for each rank.
    Top1_Result_Int = minOver(ifelse(RankDate = 1, {Result_Int}, null), [Check], PRE_AGG)

  4. Convert the int value back to a text field.
    Top1_Result = ifelse({Top1_Result_Int} = 1, ‘Success’, {Top1_Result_Int} = 0, ‘Failure’, null)

image

8

Is this correct? Shouldn’t Top1 for over_2000 should be “Failure” and Top2 for over_2000 be “Success”?