How to count Resolved tasks from other weeks?

How do I count vertically in Quicksight? I am trying to count how many tasks where resolved in each week no matter when they where created, Quicksight only counts the ones that where created that week.

*Picture example: We want that in “week 2” count the tickets that where created in “week 1” and resolved in “week 2”, also count the ones resolved in “week 2”.

*TOTAL Resolved Week 2 = 4

You can do a countOver and add some logic in it. To not count you would return a null.

countOver(ifelse({field}='true',{field},null),[])

1 Like

It works, but it gives me the value from one week, but not from all of them. If I change the = to other week it just shows from that specific week not from all of them.

Hi @Adalberto I dont think this is going to be possible as any new calculated field can only hit/evaluate each row of raw data 1 time. If we hard coded it to Week 2 then it is possible, but I assume you want to do the same for every Week in your data, which would require the calc to evaluate every other row of data before it, for each row of data.

If you join your table to itself on Week=Week (which will result in a lot more rows of data, but if we are working with a relatively small dataset then performance wont be a concern), then write a calculated field like this it should get the right numbers:
distinct_count(ifelse(Week>={Week resolved[table 2]}, ID, NULL))

Using this sample dataset, I get this result:
image

Calc for resolved same week would be:
distinct_count(ifelse(Week={Week resolved}, ID, NULL))

Calc for created that week would be:
distinct_count(ID)