Level aware aggregation pre-Agg


I have above sample data , AttemptId is a concatenation of customer ID ,CreaditCardId , Year and Month. i want to count AttemptId as perfectly test3 shows, sometimes there are duplication in AttemptId which I want to capture them, which mostly is because of month. test 3 is "
toString(concat(toString(countOver({Attempt ID},[{Attempt ID}])),‘th Attampt’))
, which I cannot use it for graph visualization , i created test 4
distinctCountOver({Attempt ID}, [{customerId-Customer},creditCardId,YearExtracted,MonthExtracted], PRE_AGG)
and after selected min for aggregation , I have tried other aggreation as well, none of them worked and the result is not matching with test3, i also tried
countOver({Attempt ID}, [{{Attempt ID}}], PRE_AGG)

if you need a sample data you can use : below copy and past in excel

customerId creditcardId Year Month resultcode
2 101 2022 1 1
2 101 2022 2 1
2 102 2022 3 1
2 102 2022 3 1
2 102 2022 3 1
2 102 2022 3 0
2 102 2022 4 1
2 102 2022 5 1
2 102 2022 6 0
i want test3 using LWA:

Hi @MaryamNajimigoshtasb
based on you small sample set. what would be your expected outcome for test3?

Hi , Thank you for the prompt reply, please see below :

I need to use the test3 in axis of a barchart, test3 should be based on column B to E , ignore F please

so like that?

Thank you. The Attempt Id should have month and
If you put 3 for last 3rows for the month, then last 3 result

become 4

No , unfortunately, it is not meets the criteria, please see above

But the attemptID includes the month.

Is the resultcode important somehow?

My bad , yes includes , the scenario is how many times a customer by using a unique credit card in a year in a months try to pay for subscription ,
Your Attempt Id is correct, but the Attempt(s) is not , 4 should be 1,
If you have multiple try in a month like in month 3 , 4 times try

At the end, i use the result code to filter this field

Do you need more clarification from my side? I really need this :weary:

HI @MaryamNajimigoshtasb
as the 3 rows for month 3 as seen as 1 row you see only 1 row without showing resultcode (right table)
if you bring the resultcode into the table (left) you will get a second row.


1 Like

Hi , I appreciate your time, in your table, I want to see 2 instead of 4, which indicates in March(3) the transaction of this customer was successful, in fact counts as the duplication of attempt ID

I modified the dashboard here


If you haven’t gone through these topics. Please ref. them; (they always end up helping me finding solution)

1 Like

Hello @MaryamNajimigoshtasb, do you still need assistance with this issue in QuickSight or were you able to find a solution with the assistance provided? Let me know if you have any further questions on this issue and I can try to guide you towards you expected output. Otherwise, if you were able to find the solution, please let us know what you implemented on your end to complete this. Thank you!

Hello @MaryamNajimigoshtasb, since we have not heard back from you, I will archive this topic. If you need further assistance with this issue, please post a new question in the community and link this topic to provide relevant information. That will ensure you will be at the top of the priority list for a response from one of our QuickSight experts. Thank you!