Hello community
I have a problem, I can’t nest aggregation functions, but I would like to be able to count the amount of users(token field in the rows) that have at least 1 record in the week(count of the id field) and also those that have no records, to be able to verify, but I can’t isar countif, ifelse doesn’t work because I can’t segment by ‘created_at’ which is the date field in the columns, I can want to know this data by week(companies with at least 1 id record), not in the total.
Hi @Juan ,
can you share a simple picture (eg. take a screenshot form it in Excel) from your desired outcome?
Best regards,
Nico
hello @Nico
Thanks you for your reply.
Here I show what is currently in quicksight in a pivot table, the rows where token is located, are my users, the date columns, are divided by weeks with my created_at field, and the values, are the amount of orders(count of id).
My ideal result, would be the total expected field, where the count of tokens(users) that have at least 1 order(count of id) would be done per week, so for the week of July 29th we have 4 tokens with at least 1 order, for the week of July 15th we have only 1 token with at least 1 order.
Hope i made myself clear in the desired outcome, I await your answer in case you find a viable solution.
Hi @Juan
Thanks for the explanation.
It seems like you need two visualizations. One for the upper part (not marked) and one for the lower one.
I think it is not possible to do both in one pivot table.
Were you able to create the calculation for “total expected”?
Best regards,
Nico
1 Like
This is not possible to create pivot table in QS like you created in excel. But you can write calculated field and add that field in your pivot table. which eventually shows another column in table.
you can write calculated filed like this: ifelse(count(id) > 0 OR isNotNull(count({id})), 1 , 0)
add this in your table and change the total from default to sum.
2 Likes
Hey @ali that work as i expected, thanks for that solution!
2 Likes