Calculated field to pull only a measure's latest monthly value when selecting multiple months

[Diego]

Hi all,

I am trying to pull only the latest monthly value for a given metric when a user is selecting multiple months in the filters. If for instance, a user selects January and February, QS is currently adding up the values for those months. In this case, I would like to display only the latest value for this measure (February’s value). I have tried the following:

1. Creating a calculated field to pull the latest month–>
   Last Month:

max({calendar_month_of_year})

2. Another calculated field to pull only the figure for the latest month selected: Subscriptions_v2:

ifelse({calendar_month_of_year}={Last Month},{subscriptions},0).

I am entering an error in the second step, because QS does not allow custom aggregations to contain both aggregate “MAX” and non-aggregated fields. I have tried making Last Month a string, but it does not work either.

Does anyone know how to pull this information?

Thanks,
Diego

[robdhondt]

Hi @Diego -

You can use this approach. You can consolidate some of the calcs if needed but I like to go step by step.

1. Create a calculated field that truncates the date to month

c_month

truncdate("MM",{Order Date})

2. Create a calculated field that retrieves the last month (After Filter).

c_last_month

maxOver({c_month},[],PRE_AGG)

3. Create a calculated field that sums only the measure values in the last month.

c_last_month_sales

sumOver(ifelse({c_month}={c_last_month},Sales,0),[],PRE_AGG)

Result:

[jzaba]

Hi @robdhondt, this was really helpful!

Is it possible to replicate this exercise having “Sales” as a calculated field? For example, having for a measure expressing the sales percentage per month?