Use two different date selections within the same sheet
I want to create a table within a sheet that uses two different types of date selection.
For the first columns in my table I can simply add a yearmonth date selection (fe. March 2023) so they return the values for that whole month.
For another column, however, I need to use another type of date selection. To be more precise: if the date selection for the first columns is March 2023, I need the date selection for that particular column to be 28th of February (last day of previous month).
I can't add them both as selection, since they are mutually exclusive. However, I'm wondering if I can define the date selection of the second value as relative to the first date selection
For example: I would use yearmonth (March 2023) selection for the whole sheet and use a function in my other column to return the value I need (something like: return value for the last business day of yearmonth - 1 month).
The reason why I want to do this in the same sheet is because I want to evaluate the yearmonth columns against the column with the value of the last date of the previous month. If I can have both of these values in the same table, I can also show the ratio's between these values.
Is it possible to do something like this or do I have to use two different sheets and then just manually calculate the ratio's between these values outside of these sheets?
It's not very difficult and you don't need two different selections because the other states could be derived from a single selection, for example you may use something like:
sum({< MonthNum = {"$(=max(MonthNum))"}>} Value)
to get the max. of MonthNum which is by a selection the selected month and with:
Of course this could be done with all kinds of period-fields and also several fields in combination and by using functions like addmonths(), monthstart(), monthend() and so on you could refer to each date/period you want.
But one thing is very important you need to use the right dimensions within the objects. This means the above example will work with Year as dimension but not with YearMonth. The reason is not the selection state itself else that the set selection don't fit to the dimension-value because the previous month data don't belong to the current month.
