I’m trying to create a calculated field that I can use on a pivot table to show whether a specific item was purchased. Instead of saying “Not Purchased” the result in the pivot table says “purchased” and leaves blank where an item wasn’t purchased rather than noting that it was not purchased. Below is the calculation used.
ifelse(isNull(Product),“Not Purchased”, “Purchased”)
Also I would appreciate any ideas for visualizing this in a cleaner way. Thanks!
Hi @Sophia_Koch and welcome to the Quick Community!
Based on your sample in Arena and the dataset being utilized, you won’t be able to achieve this with your current layout unfortunately. Quick Sight does not have the ability to essentially create rows that don’t exist in your dataset. By the looks of it, there are currently no rows that designated for a person and product combination if there is no quantity/count. So for instance, there’s no row with the values ‘Amanda’, ‘Scissors’ with a sales of ‘0’. Therefore, the isNull function will not work in this case as there are technically no nulls, the rows just don’t exist at all.
So to get the expected behavior, you would need additional rows in your dataset that list ‘0’ or ‘null’ when they do not have any sales as opposed to having no row at all.
Hi @Sophia_Koch,
Following up here as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you
Hi @Sophia_Koch,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
Thank you
