Bring associated dimension (string) with the minimum date without having NULL values

[ecmeydan]

Hello all, I am working on two calculated fields to bring 1/the name of the product which has the soonest offer end date as STRING and 2/soonest offer end date as DATE per account. You can find the data I have below and required calculated fields highlighted with red.

By leveraging this post, I managed to have second calculated field (2) which is the soonest offer end date as DATE:

minOver({offer_end_date}, [{account}], PRE_AGG)

However, the recommended solution in the post does not lead to expected outcome for dimensions.

ifelse((minOver({offer_end_date}, [{account},{product}], PRE_AGG) = {offer_end_date}), {product},NULL)

The formula above leads to NULL values in the products which do not associate with the soonest offer end date. After having required calculated fields, we will groupby accounts and have 1 record per account with soonest_offer_end_date and upcoming_product.

Being able to take “Max” of ifelse statement would have been the workaround as NULL values would have been neglected. However currently it is not possible.

Thank you for your support in advance.

[Peter]

Hi ecmeydan,

Have you tried adding a filter on your field that is returning NULL values and then using that filter to exclude NULL values? If the NULL value is being returned in the filter list options, you can uncheck that box that says NULL (or use exclude and only check NULL) or you can use the custom filter type, set it to “Does not equal”, then enter a space in the value section and select “Exclude nulls”.

Does this solution work for you? I am marking this reply as, “Solution,” but let me know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

[ecmeydan]

Hi Peter, thanks for your reply. Unfortunately filtering is not a solution for us, as this calculated field is used in a complex pivot table with other calculated fields. Filtering the underlying data affects other calculated fields and removes valuable information.

[Peter]

Thanks for the clarification. Can you test if using PRE_FILTER in your calculated fields instead of PRE_AGG resolves this issue?

[ecmeydan]

@Peter

PRE_FILTER

Thanks for the suggestion, unfortunately it did not change the output.

[ArunSanthosh]

Hi Ecmeydan,

Peter’s suggestion to use PRE_FILTER calculation level and then filter out the null values from Upcoming_Product calculation will work. I see your note in one of the replies that you don’t want to apply filter as it impacts your other calculations. So, you probably just tried changing calculation level to PRE_FILTER without adding the filter to exclude null values. In that case, the result wouldn’t change.

Right way to tackle this is to change the other calcs (that you don’t want to be impacted by filters) to PRE_FILTER mode as well. Here’s a screen shot showing the results with the PRE_FILTER calculation level and null filter.
Soonest_Offer_End_Date : minOver({Offer_End_Date},[Account],PRE_FILTER)
Upcoming_Product:ifelse({Offer_End_Date} = {Soonest_Offer_End_Date}, Product, NULL)

If you want to replace the null values in Upcoming_Product with the max value from that Account, you can use lastValue function. However, this being a table function, will require Upcoming_Product field to be available in the visual (at least in hidden state) and hence will result in two rows showing up for the account.
Last Value of Upcoming_Product:lastValue({Upcoming_Product},[{Upcoming_Product} ASC],[Account])

I think it will be beneficial to have a lookup function to better address scenarios like this.
I have put in a Product Feature Request for same. (This is an internal link). You can have your AWS Technical Account Manager (TAM) add a customer influence to this PFR. If your org doesn’t have a dedicated TAM, send me your org details at arunsant@amazon.com and I will add the customer influence on your behalf.

I’m marking this as the solution for your issue.

Regards,
Arun Santhosh